Forum Discussion

Kitu11's avatar
Kitu11
Frequent Visitor
3 years ago

Calculate the time difference

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

2 Replies

  • Kitu11's avatar
    Kitu11
    Frequent Visitor

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

    • Kitu11's avatar
      Kitu11
      Frequent Visitor

       

      Spoiler
       

      Hello all,

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

      Kate