Forum Discussion
Calculate rolling values in DAX (Tabular Model)
Dear all,
I have to create 3 measure in Tabular model.
1) SUM of values of the last 12 monthes with end date the MAX selected date
2) SUM of values starting from -12 monthes to -24 monthes
3) SUM of values starting from -24 monthes to -36 monthes
The first formula orks fine, this is the code:
Last 12 Month Rolling:=
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX ( 'Posting Date'[Date] )
Return
CALCULATE([day],
DATESINPERIOD ( 'Posting Date'[Date], LastCurrentDate, - NumOfMonths, MONTH ))
In the second formula I would have to replace LastCurrentDate with DATEADD (LastCurrentDate, -1, YEAR)
I tried with this one:
12-24 Month Rolling:=
VAR NumOfMonths = 12
//VAR LastCurrentDate = MAX ( 'Posting Date'[Date] )
Return
CALCULATE([day],
DATESINPERIOD ( 'Posting Date'[Date], DATEADD( MAX ( 'Posting Date'[Date] ),-1,YEAR), - NumOfMonths, MONTH ))
But I got a semantic error on DATEADD function.
Can you help me to solve the problem?
Thank you,
Simona
Hi simonap ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Date = DISTINCT('Table'[Date])2. Use Enter data to create a Slicer table.
3. Create measure.
Flag1 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-1,MONTH(_select),DAY(_select))&&MAX('Table'[Date])<=_select,1,0)Flag2 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-2,MONTH(_select),DAY(_select)) &&MAX('Table'[Date])<=DATE( YEAR(_select)-1,MONTH(_select),DAY(_select)),1,0)Flag3 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-3,MONTH(_select),DAY(_select)) &&MAX('Table'[Date])<=DATE( YEAR(_select)-2,MONTH(_select),DAY(_select)),1,0)Flag = SWITCH( TRUE(), MAX('Slicer table'[Slicer])="last 12 monthes",[Flag1], MAX('Slicer table'[Slicer])="-12 monthes to -24 monthes",[Flag2], MAX('Slicer table'[Slicer])="-24 monthes to -36 monthes",[Flag3])Sum_Total = SUMX(FILTER(ALLSELECTED('Table'), 'Table'[Date]<=MAX('Table'[Date])),[Amount])4. Place [Flag]in Filters, set is=1, apply filter.
5. Result:
Use [Slicer] of the Slicer table and [Date] of the Data table as slicers, select last 12 monthes, and display the rolling total for that interval
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
johnt75Super User
Try
DATESINPERIOD ( 'Posting Date'[Date], DATEADD( 'Posting Date'[Date],-1,YEAR), - NumOfMonths, MONTH ))Hi simonap ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Date = DISTINCT('Table'[Date])2. Use Enter data to create a Slicer table.
3. Create measure.
Flag1 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-1,MONTH(_select),DAY(_select))&&MAX('Table'[Date])<=_select,1,0)Flag2 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-2,MONTH(_select),DAY(_select)) &&MAX('Table'[Date])<=DATE( YEAR(_select)-1,MONTH(_select),DAY(_select)),1,0)Flag3 = var _select=SELECTEDVALUE('Date'[Date]) return IF( MAX('Table'[Date])>=DATE( YEAR(_select)-3,MONTH(_select),DAY(_select)) &&MAX('Table'[Date])<=DATE( YEAR(_select)-2,MONTH(_select),DAY(_select)),1,0)Flag = SWITCH( TRUE(), MAX('Slicer table'[Slicer])="last 12 monthes",[Flag1], MAX('Slicer table'[Slicer])="-12 monthes to -24 monthes",[Flag2], MAX('Slicer table'[Slicer])="-24 monthes to -36 monthes",[Flag3])Sum_Total = SUMX(FILTER(ALLSELECTED('Table'), 'Table'[Date]<=MAX('Table'[Date])),[Amount])4. Place [Flag]in Filters, set is=1, apply filter.
5. Result:
Use [Slicer] of the Slicer table and [Date] of the Data table as slicers, select last 12 monthes, and display the rolling total for that interval
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly