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

[Anonymous]

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.

[Barthel · Accepted solution]

@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]

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]

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.