cancel
Showing results for
Did you mean:
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 Period =
VAR Result =
CALCULATE(
AVERAGEx(
[Unit_SUM]
),
Period
)
Return
Result``````

1 ACCEPTED SOLUTION
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.

``````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.

2 REPLIES 2
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.

``````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.

Frequent Visitor

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.

Announcements