Calculate the time difference

[Marta1]

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.

Two conditions need to be considered:

1. Weekends should be excluded from the calculation.

2. Only business hours, which are from 8:00 am to 6:00 pm, should be taken into account.

I have created the calender table to acheive that, you can get the clear view in the attached PBIX file

So, the dax calculated column i mentioned below 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

onhold time difference.pbix

drive.google.com

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)

Could you please help me here?

Thank you so much in advance

 

[Matt Allington]

Take a look at my article here and see if it helps.

Calculating Elapsed Business Hours Using DAX Part 2

I have learnt a lot over my 50 something years in life, and one of my greatest learnings is that “it doesn’t matter how much you already know, there is still more to learn”. Hopefully this is one of the reasons you read my blog – because you want to [...]Read More »

exceleratorbi.com.au