Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
As you can see from the image, I am trying to find 12 months moving average for AIFR 12 from the selected month(in the month slicer). Unfortunately, I am not able to get the desired result.
Can anyone please help me. Please find the PIBIX here PBIX
Thank you
Hi sandesh,
I've had this exact challenge in one of my reports where I had to calculate an average for the 6 months prior to the month selected in my slicer. I've adapted my solution to fit your situation:
Moving Average AI = CALCULATE(AVERAGE([AIFR 12]), DATESINPERIOD('Calendar'[Date], SELECTEDVALUE('Calendar'[Month (Current)]), -12, MONTH))
Take note however, that this formula uses a created calendar table . One of the best methods I found to calculate things with rolling dates is to have such a calendar table:
1. Go in the modeling tab and click on "New table"
2. Type: Calendar = CALENDAR(MINX('SMS Incident';'SMS Incident'[Incident Date]); MAXX('SMS Incident';'SMS Incident'[Incident Date]))
3. Add the following column in the calendar table (I used format "mmm. yyyy", but you can use another format):
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
I typically use these formulae. And they work well Date table.
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Appreciate your Kudos.
In fact, I did what you said, but now it each value is the sum of previous values. Could you please help me here. I want average.
I think [AIFR 12]) is using some time stuff, can you share the formula
The one you or this one should have worked
Moving Average AI = CALCULATE(AVERAGE([AIFR 12]),DATESINPERIOD('REF Calendar'[Date],MAX('SMS Incident'[Incident Date]),-12,MONTH))
Please find the below formulas
Hi,
Show your desired result very clearly.
Hi Ashish,
I want the moving average for the measure 'AIFR 12', from the last 12 months from the selected month.
For example in the image I have selected month 3 and I am getting the AIFR 12 for the past 12 months. But unable to get its 12MMA.
When I applied the dax
Hi,
I am confused but am willing to help. I still do not know the exact figures that you expect to see as the end result. Kindly illustrate the same in an Excel file.
@Anonymous , I checked, there is Gap in the calendar table, can you create a calendar table using calendar function and mark it as date table.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
@Anonymous , Check if measure 2 in attached(after signature) file can help
Hi,
Sorry for the delay in replying but i really cannot understand the question.
Hi Ashish,
Have you heard of the terminology called "TRIFR" Total Recorable Injuries Frequency Rate? This is very common business terminology in Australia.
I have TRIFR, but I want to calculate TRIFR (12 MMA) Please find the PBIX
In the image below they have selected "Jan-20" on their date slicer and it is displaying data for the last 12 months from the "Jan-20"
I.e I need to replicate the below image in power BI.
Hope this makes clear.
Thank you for taking interest. Please let me know if there any other way I could help you understand the requirement?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |