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 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!

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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