cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Desauv
Frequent Visitor

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

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

 

Desauv_2-1664369595409.png

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1664370971389.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1664370971389.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.