Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago
Solved

Year on Year difference

Hi,

 

I have the following table of data

 

 Year  Value

 

2021 1000

2022   1500

2023    2200

2024    2000

 

 What I'd like is 2 extra columns, the first showing the different Year on Year in a value with earliest Year showing as a 0 and the % change. 

 

Example below.

 

 Year  Value. Value_change

 

2021 1000. 0

2022   1500. 500

2023    2200. 700

2024    2000. -200

 

Then for my 2nd column, I'd like to see the percentage change of the same data

 

Year  Value. Value_change. %_change

 

2021 1000. 0. 0

2022   1500. 500. 50%

2023    2200. 700. 47%

2024    2000. -200. -10%

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi M_SBS_6 ,

    I create a table as you mentioned.

    Then I create two calculated columns and here is the DAX codes:

    Value_change =
VAR _CurrentYear = 'Table'[Year]
VAR _CurrentValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Year] = _CurrentYear - 1 )
    )
RETURN
    IF ( _CurrentValue = BLANK (), 0, 'Table'[Value] - _CurrentValue )

    %_change =
VAR _CurrentYear = 'Table'[Year]
VAR _CurrentValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Year] = _CurrentYear - 1 )
    )
RETURN
    IF ( 'Table'[Value_change] = 0, 0, 'Table'[Value_change] / _CurrentValue )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    M_SBS_6 , better to have separate year/date table and join that back on year/date 

     

    and then have measures like

     

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Value]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Value]),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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Why do you want a calculated column formula?  Why not a measure.  Also, shouldn't the answer of the last row be -9.99% (rather -10%)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi M_SBS_6 ,

    I create a table as you mentioned.

    Then I create two calculated columns and here is the DAX codes:

    Value_change =
VAR _CurrentYear = 'Table'[Year]
VAR _CurrentValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Year] = _CurrentYear - 1 )
    )
RETURN
    IF ( _CurrentValue = BLANK (), 0, 'Table'[Value] - _CurrentValue )

    %_change =
VAR _CurrentYear = 'Table'[Year]
VAR _CurrentValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Year] = _CurrentYear - 1 )
    )
RETURN
    IF ( 'Table'[Value_change] = 0, 0, 'Table'[Value_change] / _CurrentValue )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.