Calculate the total time(Hours) difference for the status "on Hold"

[Kitu11]

Hello all,

I hope you're all doing well.

I'd like to determine the total number of hours between the instances when the status was "On Hold" and the subsequent status, as depicted in the photo attached below. As you can see, the case has been put "On Hold" multiple times. Therefore, the aim is to calculate the overall duration in hours.

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 a calendar table as well

Thank you so much for your help in Advance 🙂

[vanessafvg]

please provide the data in text format for your sample data.

[Kitu11]

Hello,

Thanks for your reply
Please have a look at the attached table,

Case history table
Case number	Current Status	Created date
1234	Created	7/14/23 4:20 PM
1234	In progress	7/14/23 4:26 PM
1234	on hold	7/14/23 4:39 PM
1234	Ready to proceed	7/18/23 9:12 AM
1234	Approved	7/18/23 9:45 AM
1234	On hold	7/18/23 9:56 AM
1234	Evaluation	7/19/23 11:12 AM
1234	Approved	7/19/23 11:40 AM
1234	Closed	7/19/23 11:55 AM
1234	Closed	7/19/23 11:55 AM

[Anonymous]

Hi @Kitu11 ,

I suggest you to try code as below to create a measure.

Hours = 
VAR _NEXYDATE =
    CALCULATE (
        MIN ( 'Table'[Created date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Case number] ),
            'Table'[Created date] > MAX ( 'Table'[Created date] )
        )
    )
VAR _OtherDays =
    CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER (
            'Calendar',
            NOT ( 'Calendar'[WeekDay] IN { 6, 7 } )
                && 'Calendar'[Date] > DATEVALUE ( MAX ( 'Table'[Created date] ) )
                && 'Calendar'[Date] < DATEVALUE ( _NEXYDATE )
        )
    )
VAR _STARTDAYHOUR =
    DATEDIFF (
        MAX ( 'Table'[Created date] ),
        DATEVALUE ( MAX ( 'Table'[Created date] ) ) + TIME ( 18, 00, 00 ),
        SECOND
    ) / 3600
VAR _ENDDAYHOUR =
    DATEDIFF ( DATEVALUE ( _NEXYDATE ) + TIME ( 8, 00, 00 ), _NEXYDATE, SECOND ) / 3600
RETURN
    IF (
        MAX ( 'Table'[Current Status] ) = "On hold",
        _STARTDAYHOUR + _OtherDays * 10 + _ENDDAYHOUR
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kitu11]

Hello,
I have tried it but it didn't work : ( 
I should have given some more info

Here i have added some more info below,

I have two tables with one - many relationship, Case(One) and case history(Many) tables.
Case history stores the logs of each case.

I am currently using the calculated column in the Case table, 


time diff onhold(Business hours) =
Var start_ =
CALCULATE(
MAX('Case History'[Created date(Fixed)]),
'Case History'[NewValue] = "On Hold" &&
'Case History'[Field] = "Status" &&
'Case History'[OldValue] <> "On Hold"
)
Var end_ =
CALCULATE(
MAX('Case History'[Created date(Fixed)]),

'Case History'[NewValue] <> "On Hold" &&
'Case History'[OldValue] = "On Hold"
)

Return

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

The above code calculates only the latest time diff between the on-hold and the next status.
As shown in the photos below

1) This one is a table visual that consist of case number from the case table and the calculated column dax calculates the time difference

2) This one is a case log of each case from the case history table and this is also a table visual

Could you please help me here? 🙂

Regards
Kate