Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create a measure for last 12 month rolling average using the following formula:
12M Moving Avg = CALCULATE(AVERAGE(MasterData[Actual Sales]),DATESINPERIOD(MasterData[GL Date],LASTDATE(MasterData[GL Date]),-12,MONTH))
The thing is that when I am using this measure in line chart instead of calculating rolling 12 month average it is calculating average of the current month. I am not able to understand the cause.
Also if I am trying to create a calculated column instead of a measure I am getting an error that i do not have enough memory to perform this operation.
Any suggestions on this one?
Thanks,
Siddhant
Solved! Go to Solution.
Hi @siddhantk989,
In source table, create two calculated columns as below:
12 month ago = DATEADD ( MasterData[GL Date].[Date], -12, MONTH ) rolling average = DIVIDE ( CALCULATE ( SUM ( MasterData[Actual Sales] ), FILTER ( ALL ( MasterData ), MasterData[GL Date] > EARLIER ( MasterData[12 month ago] ) && MasterData[GL Date] <= EARLIER ( MasterData[GL Date] ) ) ), 12 )
Result.
Regards,
Yuliana Gu
Hi @siddhantk989,
In source table, create two calculated columns as below:
12 month ago = DATEADD ( MasterData[GL Date].[Date], -12, MONTH ) rolling average = DIVIDE ( CALCULATE ( SUM ( MasterData[Actual Sales] ), FILTER ( ALL ( MasterData ), MasterData[GL Date] > EARLIER ( MasterData[12 month ago] ) && MasterData[GL Date] <= EARLIER ( MasterData[GL Date] ) ) ), 12 )
Result.
Regards,
Yuliana Gu
Hi @v-yulgu-msft,
Thanks for the solution. Also can you pelase help me how to use this calculation where I want to calculate it for just past 12 months.
I mean I want to show the 12M moving average for just past 12 months
Hi @siddhantk989,
The DAX provided in my original post is calculating the moving average for past 12 months. So, I am confused about your question, would you please elaborate it with some examples?
Regards,
Yuliana Gu
It is providing rolling avergae for past 12 months. But what i want to show is moving 12 month average for just past 12 moths, i.e, what was my moving 12 m average in June 2016 - May 2017. I have data from 2012 to last day.
you might be able to get away with something like this:
12m moving average =
CALCULATE (
average('MasterData'[ActualSales]),
'MasterData'[GL Date] > TODAY()-365)
this will give average sales of the last year from the day you are viewing the report
In terms of working with that line chart try replace with this:
12m moving average =
CALCULATE (
average('MasterData'[ActualSales]),
'MasterData'[GL Date] > MAX(MasterData'[GL Date]-365))
this will hopefully give you the average for the previos year at each date interval on your graph
Hi @JordanAWARE,
Thanks for replying but the solution is not working. I am getting an error saying MAX function can not be used in True/Fasle condition.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |