cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vishal097
Helper I
Helper I

Rolling Cumulative Total Over last 3 Year based on Selected Year

 

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

vishal097_0-1653539327694.png

 

Based on current year select

Getting result as

vishal097_1-1653539327698.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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

1.png

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
Super User
Super User

Hi @vishal097 
Please try

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

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors