Rolling 12M Average only calculate average on year and not month

[Desauv]

Hi

So I can't get below DAX to work properly. It calculates the average fine on the the year basis. 

However I can't get it to calculated moving 12M average on a monthly basis (see picture below).

I hope someone way smarter than me can figure this out 🙂

Unit_R12M = 
VAR NumOfMonths = 12
VAR LastSelectedDate = MAX('data newest'[Date])
VAR Period =
DATESINPERIOD('data newest'[Date], LastSelectedDate, -NumOfMonths, MONTH)
VAR Result =
CALCULATE(
    AVERAGEx(
        DISTINCT(VALUES('data newest'[Date])),
            [Unit_SUM]
      ), 
    Period
)
Return
Result

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

Rolling 12M avg: =
VAR NumOfMonths = 12
VAR LastSelectedDate =
    MAX ( 'Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Calendar'[Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX ( DISTINCT ( VALUES ( 'Calendar'[Month Name] ) ), [Unit sum:] ),
        Period
    )
RETURN
    Result

[Desauv]

Hi Jihwan

Okay, I can now see where the problem lies. I didn't have a seperate calendar table in my datamodel. I was just using dates in the extracted data. 
I've now build a Calendar table in my datamodel, and used those dimensions in my DAX calculation for my R12 average, and now I'm getting the right result.

Thanks a lot.