Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Difference between two rows of same column for same ID using DAX

Hello Everyone, I have a table as below, where I need to calculate "ValueDiff".  There is no date for Initial situation, but date is availble for New Situation. However, the date for new situ...
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    Anonymous 

    Could you show me the table in Power BI? Maybe a reference is missing? If I add the calculated column based on your sample data it works.

Barthel's avatar
Barthel
Solution Sage
3 years ago

You could use this DAX as a calculated column:

Value Diff = 
VAR _initial =
    CALCULATE ( 
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Situation] = "Initial"
    )
VAR _new =
    CALCULATE ( 
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Situation] = "New"
    )
VAR _result =
    IF ( 'Table'[Situation] = "New", _new - _initial )
RETURN
    _result

You want to remove all context except ID. Hence ALLEXCEPT. For each variable, you specifically add the Situation as a filter. The result is the difference between the two, but you only show this for the lines where the Situation is equal to 'New'.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello Barthel,

    Thank you for your prompt repsonse. I tried the above code, however the result is not as expected.

    I have the results as below:

    For the id's that have same Inital and New values, it returns the value as New. For cases where there is a difference, it is returning the value as Initial.