Forum Discussion

kagy100's avatar
kagy100
Icon for Advocate II rankAdvocate II
5 years ago
Solved

DAX Assistance Needed

Hi Team, Here is the data table: ID             Usage       Year        Difference       Trend 52503       0               2019        52503       2.35          2020        2.35                In...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
kagy100
Icon for Advocate II rankAdvocate II
5 years ago

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's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
5 years ago

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