Calculate the time difference

Kitu11 — Fri, 04 Aug 2023 06:56:50 GMT

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

Re: Calculate the time difference

Kitu11 — Sun, 06 Aug 2023 06:03:43 GMT

Could anyone please help me with this? Any help would be much appreciated:)

Re: Calculate the time difference

Kitu11 — Tue, 08 Aug 2023 11:34:26 GMT

Hello all,

it would be really helpful if you guys give me any suggestions on this topic 🙂
Thank you from the mountain
Regards

Kate

topic Calculate the time difference in DAX Commands and Tips

Calculate the time difference

Re: Calculate the time difference

Re: Calculate the time difference