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.
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
Solved! Go to 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
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |