Calculating average units for two years

[senetabeaty74]

Hello. I am trying to calculate the average of my 2022 and 2023 unit totals. This metric is calculated by doing a DUPCOUNT([AccountId]) in my widget. Yes, I am using the dupcount on purpose. I tried making a custom column to calculate the totals for each year so i could then do ([# of 2022 Units] + [# of 2023 Units]) / 2 to get my average. 

SELECT COUNT([AccountId]) 
FROM [Table]
WHERE GETYEAR([CreatedDate]) = 2022

but that's not doing what I expected. Whenever I do a COUNT it's essentially the same as if I didn't use a date filter. Ultimately, I am trying to get the 2024 units total divided by the average of 2022 and 2023 unit totals to get my run rate. Please help. If I try doing this in my EC, it wants me to group by, but that will interfere with my dashboard. Thank you.

[akaplan]

Hello, you should be able to use Measured Values for this.

In the widget, you can create a formula that is something like: ( DUPCOUNT([AccountId]), [Years in CreatedDate] )

And if you right-click [CreatedDate], you can apply a filter to select the appropriate year.  This can be combined as needed for a larger run rate formula.

----

Alternatively, in the ElastiCube, you could create yearly custom columns like this:

AccountCreated_2022 = CASE WHEN GETYEAR([CreatedDate]) = 2022 THEN 1 ELSE 0 END

AccountCreated_2023 = CASE WHEN GETYEAR([CreatedDate]) = 2023 THEN 1 ELSE 0 END

etc.

You could then SUM() those columns within the widgets to use 2022 or 2023 counts within formulas.  This logic could also be added to an import query directly, as opposed to creating custom columns.  But this all depends on the exact way that your data is laid out.