Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago
Solved

Difference between two values in same column

Looking for a way in Power BI to calculate the difference between values in the same column. Need the day over day change to the remaining duration. I have a formula that works but an issue has come up when durations are actually increased.  I would prefer to not show negative change and the change be based off the original duration only. Please see existing formula and example data below. 

 

 

 

 

 

 

Any help is greatly appreciated!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Daxtothemax ,

    According to your description, here's my solution.

    Create a measure.

    Diff2 =
VAR _Diff =
    CALCULATE (
        MAX ( 'DS'[Hrs] ),
        FILTER (
            'DS',
            'DS'[Task] = EARLIER ( 'DS'[Task] )
                && 'DS'[Date] = MINX ( ALL ( 'DS' ), 'DS'[Date] )
        )
    ) - 'DS'[Hrs]
RETURN
    IF ( _Diff < 0, 0, _Diff )

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Daxtothemax ,

    According to your description, here's my solution.

    Create a measure.

    Diff2 =
VAR _Diff =
    CALCULATE (
        MAX ( 'DS'[Hrs] ),
        FILTER (
            'DS',
            'DS'[Task] = EARLIER ( 'DS'[Task] )
                && 'DS'[Date] = MINX ( ALL ( 'DS' ), 'DS'[Date] )
        )
    ) - 'DS'[Hrs]
RETURN
    IF ( _Diff < 0, 0, _Diff )

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

    • Daxtothemax's avatar
      Daxtothemax
      Helper I
      4 years ago

      v-yanjiang-msft thank you for this! It's getting closer.  The only issue is that I need the difference between each date.  So example below Task C should equal 8 comparing to the previous date.  Any ideas on this?