Forum Discussion
Rolling 12 Month Average
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
3 Replies
Hi GUA
I am giving below the methodology.
Breakdown of logic.
- First calculate the [yourmeasure] of whatever - sales value etc.etc. for the last 12 months using
- Last12MCounts = CALCULATE ( [yourmeasure], DATESBETWEEN ( MasterCalendar[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( MasterCalendar[Date] ) ) ), LASTDATE ( MasterCalendar[Date] ) ) )
- What it does it filters the calendar table for all the days in the last 12 months from the current date context.
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
