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

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