Rolling Average for count of unique ID by non date

[EL4BI]

Hello,

I want to create a new measure so that I can put in a 3 yr rolling/moving average count on a chart that I have plotted as count of unique ID vs Sale Yr. (see chart).  For example there would be a dot for 3.6 on 2021 that is the average of [3 (2019)+5(2020)+3(2021)]/3 = 3.6

Note that the "Sale Yr" is of whole number Data type.  Here is the chart and table.  

Thanks

[MAwwad]

To create a measure for a 3-year rolling average count, you can use the following DAX formula:

3 Year Rolling Avg Count = VAR SelectedYear = MAX('sales'[Sale Yr]) RETURN AVERAGEX( FILTER( VALUES('sales'[ID]), 'sales'[Sale Yr] >= SelectedYear - 2 && 'sales'[Sale Yr] <= SelectedYear ), CALCULATE(DISTINCTCOUNT('sales'[ID])) )

This measure calculates the average count of unique IDs for the selected year and the two previous years.

[EL4BI]

Thanks for such quick reply.  I get the following error: 

thanks

[EL4BI · Accepted solution]

I figured it out:  It should be: 

3YrRollingAvgCT =

VAR Lastyear = Max(Sales[Sale Yr])

VAR PerYr = 2

Var CTSum = CALCULATE(

    counta(Sales[ID #]),

    FILTER(all(Sales),Sales[Sale Yr]>=Lastyear-PerYr && Sales[Sale Yr]<=Lastyear))

RETURN

CTSum/(PerYr+1)