topic Re: Decade over Decade KPI in DAX Commands and Tips

Decade over Decade KPI

Arnabi — Sun, 05 Dec 2021 09:02:05 GMT

I'm Trying to create a DoD and DoD % Change KPI, to go along with my YoY KPI and YoY change. I was able to create a Decade column on my date table using the following DAX code (if someone has a better one do share)

Decade =

VAR DecadeStart =

ROUNDDOWN( DateDimension[Year], -1)

VAR DecadeEnd = DecadeStart + 9

RETURN

DecadeStart & " - " & DecadeEnd

but i want to calculate the change of a measure over a decade rather than year and place it on the graph as well . My current YoY and %YoY change Dax code is

LYNationWideRatio = CALCULATE([NationWideHomePriceRatio], SAMEPERIODLASTYEAR(DateDimension[Date]))

%YoYNationWideRatio = DIVIDE([DiffNationWideRatio], [LYNationWideRatio], 0)

Re: Decade over Decade KPI

ValtteriN — Sun, 05 Dec 2021 13:10:56 GMT

Hi Arnabi,

To calculate DoD we can use datesbetween in a calculate function. Here is how I would approach this:
My test data:

I created the decade column the same way you made it:

Decade =

VAR DecadeStart =

ROUNDDOWN( YEAR('Values DoD'[Date]), -1)

VAR DecadeEnd = DecadeStart + 9

RETURN

DecadeStart & " - " & DecadeEnd

For DoD calculation I used the following DAX:

DoD =

var CYear = year(max('Values DoD'[Date]))

var EODecade = date(roundup(

if(right(CYear,1)="0",

CYear+1,

CYear)

,-1)-1,12,31)

//Here I define end of decade based on selection. So for example 2011.1.1 would return 2020.31.12. The If condition is in place for years thath are multiple of 10

var SODecade = date(ROUNDDOWN(CYear,-1),1,1) return //same for start of decade

CALCULATE (

SUM('Values DoD'[Value]),

DATESBETWEEN (

'Values DoD'[Date],

SODecade,

EODecade

)

) //Actual DoD calculation

Last decade can be calculated similarly:

Last Decade DoD =

Var CYear = year(max('Values DoD'[Date]))

var EODecade = date(roundup(

if(right(CYear,1)="0",

CYear+1,

CYear)

,-1)-11,12,31)

var SODecade = date(ROUNDDOWN(CYear,-1)-10,1,1) return

CALCULATE (

SUM('Values DoD'[Value]),

all('Values DoD'),

DATESBETWEEN (

'Values DoD'[Date],

SODecade,

EODecade

)

) //Here the difference is -11 and -10 in the variables. Also we need to use All due to Decade column

Finally DoD difference:

DoD difference % = DIVIDE([DoD]-[Last Decade DoD],[Last Decade DoD])

End result:

Hopefully this helps and if it does consider accepting this as a solution!

Re: Decade over Decade KPI

Arnabi — Mon, 06 Dec 2021 17:01:12 GMT

I greatly appreciate it...it worked beautifully