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                Increase

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

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      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...

  • Anonymous's avatar
    Anonymous
    Not applicable
    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