topic Help Required For Calculating Rolling Last 6 Month Average using DAX in Power BI in DAX Commands and Tips

Help Required For Calculating Rolling Last 6 Month Average using DAX in Power BI

mitull — Sat, 27 Aug 2022 06:41:17 GMT

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!

Re: Help Required For Calculating Rolling Last 6 Month Average using DAX in Power BI

ValtteriN — Sat, 27 Aug 2022 13:24:00 GMT

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 SELECTEDMEASURE

VAR _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/

Re: Help Required For Calculating Rolling Last 6 Month Average using DAX in Power BI

mitull — Fri, 16 Sep 2022 06:39:21 GMT

Hi ValtteriN

Thank you very much for your quick help ! Your solution worked for me.

Thanks again and stay blessed !!!