Calculating Duration Change

[Daxtothemax]

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!

[Anonymous · Accepted solution]

Hi @Daxtothemax ,

You can create another new calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _negdate =
    CALCULATE (
        MAX ( DS[Date] ),
        FILTER ( DS, DS[Task] = EARLIER ( DS[Task] ) && DS[Change] < 0 )
    )
VAR _negcount =
    CALCULATE (
        COUNT ( DS[Change] ),
        FILTER ( DS, DS[Task] = EARLIER ( DS[Task] ) && DS[Change] < 0 )
    )
VAR _mindvalue =
    CALCULATE (
        MAX ( DS[Remaining Duration (Hours)] ),
        FILTER ( DS, DS[Task] = EARLIER ( DS[Task] ) && DS[Date] = MIN ( DS[Date] ) )
    )
RETURN
    IF (
        ISBLANK ( _negcount ),
        [Change],
        IF ( DS[Change] < 0, 0, IF ( DS[Date] < _negdate, DS[Change], _mindvalue ) )
    )

Best Regards

[Greg_Deckler]

@Daxtothemax This is a variation of MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous