Forum Discussion

kagy100's avatar
kagy100
Advocate 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

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    kagy100
    Advocate 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
      Memorable 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...