Calculate the time difference

Kitu11 · ‎08-03-2023

Hello all,

I hope you're all doing well.

I want to determine the total number of hours between the instances when the status was "On Hold" and the subsequent status.

At this moment, I am using a calculated column

Total onhold difference(Business hours) = 
Var start_ = 
CALCULATE(
    MAX('Case history'[Created Date]),
    'Case history'[New Value] = "On Hold" &&
    'Case history'[Field] = "Status"
)

Var end_ = 
CALCULATE(
    MAX('Case history'[Created Date]),
    'Case history'[Old Value] = "On Hold" &&
    'Case history'[New Value] <> "On Hold" &&
    'Case history'[Field] = "Status"
)

return 
SUMX(
    CALCULATETABLE(
        'Calendar table',
        DATESBETWEEN('Calendar table'[Date],start_,end_),
        'Calendar table'[weekday] = 1
    ),
    MAX(MIN('Calendar table'[end],end_)-MAX('Calendar table'[start],start_),0)*24)

This calculated column Only calculates the latest on hold time difference because of Max Function,

As you can see in the photo below, the time which was marked in a black spot was excluded but I want to also include it in the calculation.

I have attached the link to the power bi file
https://drive.google.com/file/d/1wtJIpwRktMROzQPfL3HSoecDEXbDj2K2/view?usp=sharing

Could you please help me here?

Thank you so much in advance