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,
I am trying to calculate a Rolling 12 Month, but my DAX expression is failing. Can somone help me, please?
Rolling 12 Month Average = CALCULATE(AVERAGE('KPI_NumberOfIncidents (2)'[NUMBER OF INCIDENTS]);
DATESINPERIOD('KPI_NumberOfIncidents (2)'[YEAR_MONTH];'KPI_NumberOfIncidents (2)'[YEAR_MONTH];-11;MONTH)
)
Thanks!
- Gitte
Hi @GUA
I am giving below the methodology.
Breakdown of logic.
Let us say you are showing the data for July 2017. Then we take the last visible date in the month using
LASTDATE ( MasterCalendar[Date] which will return 31 Jul 2017.
Then SAMEPERIODLASTYEAR is evaluated as SAMPEPERIODLASTYEAR( 31 Jul 2017) which will return 31 Jul 2016.
This is then wrapped with NEXTDAY function to return the value 01 AUG 2016.
Then DatesBetween 01 AUG 2016 and 31 JUL 2017 represents the whole year.
This assumes you have a date ( master calendar) dimension table.
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi,
Unfortunately I don't have a date dimension table. 😞
I have tried som other stuff, but now I am stucked again...
- Gitte
Hey,
You can use a 'quick measure' to get the rolling average pretty easy.
As long as your data is clean
Greetings
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |