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 situation emains same for all id as, it depends when the data was loaded.Now I need to find teh difference between New Situation and Initial situation for same ID and produce results when there is difference.

Could you please help me out here. I am learning DAX and not able to go to solution as date is same for all ID's.

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.

3 Replies

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

       

       

  • Barthel's avatar
    Barthel
    Solution Sage
    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.