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.
Hello everyone
Noob again.
I'm trying to create a chart to show the 12-month moving average for a measure that calculates the frequency of injuries that occurred for every million hours worked. At the moment I'm stuck on how to accomplish this, as I can only get it to show the value for each month.
This is what I'm doing.
@Anonymous , You can use formula like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) /12
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-12,MONTH)) /12
Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],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/
See this post on SQLBI.com for a clear walkthrough on how to do this and a formula you can adapt for your use case.
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
If this works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |