DAX Assistance Needed

[kagy100]

Hi Team,

Here is the data table:

ID             Usage       Year        Difference       Trend

52503       0               2019       

52503       2.35          2020        2.35                Increase

52503       4               2021        1.85                Increase
Appreciate your time. I am trying to obtain the Difference Column via DAX formula.

[v-jayw-msft · Accepted solution]

Hi @kagy100 ,

You could create a index column for Financial Year. Here I used RANKX() function to get the column.

index = RANKX('Table','Table'[Year],,ASC,Dense)

Then use EARILER() function as below.

Column = 'Table'[Usage]-CALCULATE(SUM('Table'[Usage]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[index]=EARLIER('Table'[index])-1))

Best Regards,

Jay

[kagy100]

Thank you for the kind assistance.

[amitchandak]

@kagy100 , Create a separate year table and with help from that, have measures like

This Year = CALCULATE(sum('Table'[Usage]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Usage]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[kagy100]

Thank You Amit. Any tips on how to handle if the year is a Financial Year . . .

For example, 2017-2018, 2018-2019, 2019-2020

[AilleryO]

Hi, 

It should work as well, since the max will select the last in alphabetical order, and min the first.

You might have to use VALUE funtion (convert text to number) for calculation.

Test and let us know...