Calculate rolling values in DAX (Tabular Model)

[simonap]

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

[Anonymous · Accepted solution]

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

[johnt75]

Try

DATESINPERIOD ( 'Posting Date'[Date], DATEADD( 'Posting Date'[Date],-1,YEAR), - NumOfMonths, MONTH ))