Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have been working on 12month running average from long time and I am not able to figure out why I am getting some random values instead of getting my 12M rolling average.
I tried solutions fom both of the below 2 posts but not getting results from them as well.
http://community.powerbi.com/t5/Desktop/Past-12-Months-sales-vs-rolling-average/m-p/184757
Below is the link to the sample data and pbix file on which I am working.
any kind of help will be great.
Thanks,
Siddhant
Solved! Go to Solution.
Hi @siddhantk989,
In your scenario, you need to calculated the monthly total first. Please modify the measure [Rolling Average measure] as below:
Monthly total = CALCULATE ( SUM ( MasterData[Actual Sales] ), ALLEXCEPT ( MasterData, MasterData[GL Date].[Year], MasterData[GL Date].[Month] ) ) Rolling Average measure = DIVIDE ( CALCULATE ( [Monthly total], FILTER ( ALL ( MasterData ), MasterData[GL Date] > MAX ( MasterData[12 month ago] ) && MasterData[GL Date] <= MAX ( MasterData[GL Date] ) ) ), 12 )
Regards,
Yuliana Gu
Hi @siddhantk989,
Based on my original post in this old thread: Getting monthly average instead of moving 12 month average in a line chart, rather than creating calculated column to generate the moving average, you could create measures like below:
Rolling Average measure = DIVIDE ( CALCULATE ( SUM ( MasterData[Actual Sales] ), FILTER ( ALL ( MasterData ), MasterData[GL Date] > max ( MasterData[12 month ago] ) && MasterData[GL Date] <= MAX( MasterData[GL Date] ) ) ), 12 ) Lastyear-date = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ) lastmonth-date = IF ( MONTH ( TODAY () ) = 1, DATE ( YEAR ( TODAY () ) - 1, 12, 1 ), DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ) ) Moving 12M average = CALCULATE ( MasterData[Rolling Average measure], FILTER ( MasterData, MasterData[GL Date] >= MasterData[Lastyear-date] && MasterData[GL Date] <= MasterData[lastmonth-date] ) )
Best regards,
Yuliana Gu
Thanks for replying. Even by creatign measures instead of columns it stil does not works. I can still see some random values poping up isntead of 12M average.
Hi @siddhantk989,
In your scenario, you need to calculated the monthly total first. Please modify the measure [Rolling Average measure] as below:
Monthly total = CALCULATE ( SUM ( MasterData[Actual Sales] ), ALLEXCEPT ( MasterData, MasterData[GL Date].[Year], MasterData[GL Date].[Month] ) ) Rolling Average measure = DIVIDE ( CALCULATE ( [Monthly total], FILTER ( ALL ( MasterData ), MasterData[GL Date] > MAX ( MasterData[12 month ago] ) && MasterData[GL Date] <= MAX ( MasterData[GL Date] ) ) ), 12 )
Regards,
Yuliana Gu
Sorry for the late reply, i was actually stuck with some other work but thansk a lot for helping. The solution is working fine now.
Thanks,
Siddhant
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |