Forum Discussion
Help Required For Calculating Rolling Last 6 Month Average using DAX in Power BI
Hello Community,
I need your help in Power BI to create a Matrix just like below:
I have created DAX formula to calculate value for [Jan-21] to [Dec-21]. Now, I am facing challenges calculating rolling average of percentage for every last 6 months.
This matrix structure should be created dynamically if custom year-month slicer is used. E.g. if I select year-month range from "Jan-2019" to "July-2022", it should generate above report along with rolling last 6 monthly percentage average.
Any help will be highly appreciated !!!
Thanks!
Hi,
For calculating last 6m months you can use this kind of structure. Here I am using a calculation group but for a measure just place your logic where there is SELECTEDMEASUREVAR _MaxDate = MAX( 'Calendar'[Date] ) RETURN CALCULATE(SelectedMeasure(), DATESINPERIOD( 'Calendar'[Date], _MaxDate, -6, MONTH ), ALL('Calendar') )I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
2 Replies
Hi,
For calculating last 6m months you can use this kind of structure. Here I am using a calculation group but for a measure just place your logic where there is SELECTEDMEASUREVAR _MaxDate = MAX( 'Calendar'[Date] ) RETURN CALCULATE(SelectedMeasure(), DATESINPERIOD( 'Calendar'[Date], _MaxDate, -6, MONTH ), ALL('Calendar') )I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
