Calculate the time difference

anonymous228 · August 9, 2023, 4:39pm

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:

Weekends should be excluded from the calculation.
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

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)

onhold time difference.pbix (101.9 KB)

Could you please help me here?

Thank you so much in advance

Regards
Kate

HufferD · August 9, 2023, 6:47pm

I think I understand what you are after. Just for clarification, is this what you are shooting for:

The block at the top tabulates the sum of fractional hours on hold per Case Number after adjusting for weekends and business hours.

The block at the bottom shows the details for each status. I’ve highlighted the rows that include on hold statuses.

anonymous228 · August 9, 2023, 7:04pm

Hello HufferD,

Thank you so much for your fast reply
Actually, what i am trying to calculate here is the time difference between the
New value = “On Hold” and Old Value = “On Hold”

Actually, You’re almost there to the desired interpretation However, I would to determine the time gap between the marked hours in the attached image provided below.

Could you please help me?

Thank you so much in advance

Regards
Kate

HufferD · August 9, 2023, 7:06pm

right. so more like this:

anonymous228 · August 9, 2023, 7:07pm

Woooooow, Yes!!! this is what i would like to have

HufferD · August 9, 2023, 7:25pm

Okay. Here’s a modified version of your original .pbix file.
onhold time difference.pbix (95.6 KB)

I added a few measures and columns:

Adjusted Business Fractional Hours

This measure calculates the difference in time (in fractional hours) between the Created Date and the Subsequent Time of a case.
The calculation only considers business hours (8:00 am to 6:00 pm).
Weekends are excluded from the calculation.
This is achieved by breaking down the time difference into different parts: the business hours on the starting day, the business hours on the ending day, and the full business days in between. It then sums these components up and subtracts any weekend time.

Adjusted Business Fractional Hours = 
VAR StartDate = 'Case History'[Created Date]
VAR EndDate = 'Case History'[Subsequent Time]
VAR BusinessStartHour = 8
VAR BusinessEndHour = 18
VAR MinutesInBusinessDay = (BusinessEndHour - BusinessStartHour) * 60

VAR StartDayMinutes = 
IF(WEEKDAY(StartDate,2) < 6,
    SWITCH(
        TRUE(),
        HOUR(StartDate) * 60 + MINUTE(StartDate) < BusinessStartHour * 60, MinutesInBusinessDay,
        HOUR(StartDate) * 60 + MINUTE(StartDate) >= BusinessEndHour * 60, 0,
        BusinessEndHour * 60 - (HOUR(StartDate) * 60 + MINUTE(StartDate))
    ),
    0
)

VAR EndDayMinutes = 
IF(WEEKDAY(EndDate,2) < 6,
    SWITCH(
        TRUE(),
        HOUR(EndDate) * 60 + MINUTE(EndDate) <= BusinessStartHour * 60, 0,
        HOUR(EndDate) * 60 + MINUTE(EndDate) > BusinessEndHour * 60, MinutesInBusinessDay,
        (HOUR(EndDate) * 60 + MINUTE(EndDate)) - BusinessStartHour * 60
    ),
    0
)

VAR FullDaysBetween = DATEDIFF(StartDate, EndDate, DAY) - 1

VAR TotalWeekendDays = 
SUMX(
    FILTER(
        ALL('Case History'[Created Date]),
        'Case History'[Created Date] > StartDate && 'Case History'[Created Date] < EndDate && WEEKDAY('Case History'[Created Date],2) > 5
    ),
    1
)

VAR TotalBusinessMinutes = 
FullDaysBetween * MinutesInBusinessDay + StartDayMinutes + EndDayMinutes - TotalWeekendDays * MinutesInBusinessDay

RETURN TotalBusinessMinutes / 60.0

Adjusted Fractional Hours On Hold

This measure calculates the total adjusted business fractional hours for records where the status transitioned to “On Hold” (using the New Value column).
It aggregates the Adjusted Business Fractional Hours for each record where the status changed to “On Hold”.

Adjusted Fractional Hours On Hold = 
SUMX(
    FILTER(
        'Case History',
        'Case History'[New Value] = "On Hold"
    ),
    'Case History'[Adjusted Business Fractional Hours]
)

Subsequent Time

This calculated column determines the time of the next status change for each record in the ‘Case History’ table.
It searches for the next smallest Created Date that’s greater than the current row’s Created Date and shares the same Case Number.

Subsequent Time = 
CALCULATE(
    MIN('Case History'[Created Date]),
    FILTER(
        'Case History',
        'Case history'[ Case Number] = EARLIER('Case history'[ Case Number]) &&
        'Case History'[Created Date] > EARLIER('Case History'[Created Date])
    )
)

Total Adjusted Hours by Case

This measure aggregates the total adjusted business fractional hours for each unique Case Number.
It sums up the Adjusted Business Fractional Hours for each Case Number and provides a consolidated value.

Total Adjusted Hours by Case = 
SUMX(
    SUMMARIZE('Case History', 'Case history'[ Case Number], "TotalHours", SUM('Case History'[Adjusted Business Fractional Hours])),
    [TotalHours]
)

Unadjusted Fractional Hours

This measure calculates the raw time difference in fractional hours between the Created Date and the Subsequent Time of a case.
Unlike the “Adjusted Business Fractional Hours”, this measure does not account for business hours or weekends; it simply gives the raw time difference.

Unadjusted Fractional Hours = 
DATEDIFF( 'Case history'[Created Date], [Subsequent Time], MINUTE)/60

hth

anonymous228 · August 9, 2023, 7:39pm

Thank you so much for your help

I am gonna check it now

anonymous228 · August 9, 2023, 9:35pm

Hello HufferD,

I just checked it out, there is one small thing to be modified.

Actually, it doesn’t exclude the weekends, as you can see in the below photo

onhold time difference(1).pbix (101.9 KB)

Could you please help me with this modification?

Thank you from the Mountain

Regards
Kate

HufferD · August 10, 2023, 1:14am

Ah. I see. The measure does consider weekends when calculating the start and end day minutes, but when computing the full days between the start and end dates, it doesn’t explicitly exclude weekends. This replacement should account for it:

Revised Measure

Adjusted Business Fractional Hours = 
VAR StartDate = 'Case History'[Created Date]
VAR EndDate = 'Case History'[Subsequent Time]
VAR BusinessStartHour = 8
VAR BusinessEndHour = 18
VAR MinutesInBusinessDay = (BusinessEndHour - BusinessStartHour) * 60

VAR StartDayMinutes = 
IF(WEEKDAY(StartDate,2) < 6,
    SWITCH(
        TRUE(),
        HOUR(StartDate) * 60 + MINUTE(StartDate) < BusinessStartHour * 60, MinutesInBusinessDay,
        HOUR(StartDate) * 60 + MINUTE(StartDate) >= BusinessEndHour * 60, 0,
        BusinessEndHour * 60 - (HOUR(StartDate) * 60 + MINUTE(StartDate))
    ),
    0
)

VAR EndDayMinutes = 
IF(WEEKDAY(EndDate,2) < 6,
    SWITCH(
        TRUE(),
        HOUR(EndDate) * 60 + MINUTE(EndDate) <= BusinessStartHour * 60, 0,
        HOUR(EndDate) * 60 + MINUTE(EndDate) > BusinessEndHour * 60, MinutesInBusinessDay,
        (HOUR(EndDate) * 60 + MINUTE(EndDate)) - BusinessStartHour * 60
    ),
    0
)

VAR FullDaysBetween = DATEDIFF(StartDate, EndDate, DAY) - 1

VAR TotalWeekendDaysBetween = 
COUNTROWS(
    FILTER(
        GENERATESERIES(0, FullDaysBetween, 1),
        WEEKDAY(StartDate + [Value], 2) > 5
    )
)


VAR TotalBusinessMinutes = 
(FullDaysBetween - TotalWeekendDaysBetween) * MinutesInBusinessDay + StartDayMinutes + EndDayMinutes

RETURN TotalBusinessMinutes / 60.0

anonymous228 · August 10, 2023, 10:52am

Thank you so much for your help