Rolling Cumulative Total Over last 3 Year based on Selected Year

[vishal097]

I have scenario to show “cumulative total in each year” so that if any month is selected then it will show sum till that month only in that year.

we can get "cumulative total in each year" by DatesYTD and it works based on month filter also.

But then I want to show this result for last 3 year based on current year selected in year filter.

I have written measure like 

Cumulative Rolling KPI Score =

Var ScoreYTD =

CALCULATE( SUM( 'KPI Fact'[KPI Actual] ), DATESYTD( DimDate[Date] ))

Var Result =

CALCULATE( ScoreYTD ,

DATESINPERIOD(DimDate[Date], MAX( DimDate[Date]) , -3, YEAR )

Return

Result

But this show result only for 1 year instead of for last three year.

Expected result

Based on current year select

Getting result as

[tamerj1 · Accepted solution]

Hi @vishal097 

Here is the sample file with the solution https://www.dropbox.com/t/jkfCc7wHtRD4drXx

Trailing KPI Score = 
VAR NumOfYear = -3
VAR ReferenceDate = MAX ( 'Date'[Date] )
VAR PreviousDates = 
    FILTER ( 
        DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, NumOfYear, YEAR ), 
        MONTH ( 'PreviousDate'[Date] ) <= MONTH ( ReferenceDate ) 
    )
VAR Result =
    CALCULATE (
        SUM ( 'KPI Fact'[KPI Actual] ), 
        'KPI Fact'[Performance Mark] = "Executive",
        REMOVEFILTERS ( 'Date' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'PreviousDate'[Date], 'Date'[Date] )
    )
 RETURN 
    Result

[vishal097]

Thanks so much Sir.

I really don't know how to say thanks to you.

You are really a DaxExpert.

This solution definitely gone helpful to many.

[tamerj1]

Hi @vishal097 
Please try

Cumulative Rolling KPI Score =
CALCULATE (
    SUM ( 'KPI Fact'[KPI Actual] ),
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -3, YEAR )
)

[vishal097]

Well this I have already tried but my requirement is to show cummulative total based on month also
like if Decmber is selected it will show total from January to Decmber
and if June is selected it will show total from January to June

for all three year.