cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## 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

Based on current year select

Getting result as

1 ACCEPTED SOLUTION
Super User

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``````

4 REPLIES 4
Super User

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``````

Helper II

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.

Super User

Hi @vishal097

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

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors