Help creating rolling average of a distinct counted column

[andrewmvu41]

PBI Gods... I am looking to do a rolling average of a distinct counted column. I got as far as the following DAX: Rolling Avg = CALCULATE(DISTINCTCOUNT(Incidents[Incident #]), DATESINPERIOD('Date Calendar'[Date], MAX('Date Calendar'[Date]), -2, MONTH), FILTER( BU, BU = "Sales"))

, it doesn't do exactly what I want. It is giving me the rolling sum of the values instead of average. I did try the following DAX below also, but it also didn't give me what I needed. 

L&T Rolling Avg 2 =

AVERAGEX(DATESYTD('Date Calendar'[Date]), CALCULATE(DISTINCTCOUNT(Incidents[RecInj]), DATESBETWEEN('Date Calendar'[Date], MAX('Date Calendar'[Date]) - 364, MAX('Date Calendar'[Date])),FILTER(BU, BU[tbl] = "Sales")))

The table looks something like below:

4/1/2023	2
3/1/2023	1
2/1/2023	1
12/1/2022	1
11/1/2022	1
10/1/2022	2
8/1/2022	2
7/1/2022	3
5/1/2022	1
4/1/2022	5

[jdbuchanan71 · Accepted solution]

Write a measure that is just the distinct incident count.

Incident Count = DISTINCTCOUNT ( Incidents[Incident #] )

Then you can use that in a your rolling average measure.  You just have to find the hightest date you are lookin at and then we can manipulate the date range being used.

Rolling 2 Avg = 
VAR _MaxDate = MAX ( DATES[Date] )
RETURN
AVERAGEX ( 
    CALCULATETABLE ( 
        VALUES ( 'Date Calendar'[Month Year] ), 
        DATESINPERIOD( 'Date Calendar'[Date], _MaxDate, -2, MONTH )
    ),
    [Incident Count]
)

This will give us the average of the current month and previous month amounts for each month.