Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago
Solved

Calculating Duration Change

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 ...
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Greg_Deckler
Community Champion
4 years ago

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/339586.
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