Calculate the solution time between two log moments based on working hours and business days

[Anonymous]

I want to calculate the solution time between two log moments (date_time finished  AND date_time required) based on working hours and business days. By this I mean that weekends, vacations, public holidays and leisure time are excluded from the formula. This will make my results more valid. 

For now I have used DATEDIFF(bw_Cofiworkorders[Date_time finished],bw_Cofiwerkorders[Date_time required],SECOND). Which gives me te result between two dates in seconds. But it does not meet the above requirements.

It also needs to be possible to get negative results like this; in hours: -34:13:43 or in seconds: -14543.

An example: 

Friday 25-2-2022 at 16:00 and Monday 28-2-2022 at 11:00. The answer needs to be 4 hours(I prefer the answer to be in seconds, so 14400). Because the workday ends at 17:00 on friday, so from 16 it is 1 hour. And starts again at Monday 8:00, till 11 it is 3 hours. So the result is 4 hours.

Who can help me?

[DataInsights]

@Anonymous,

This calculated column requires a date table with a Weekday Flag column (no relationship between the date table and fact table is required).

Workday Seconds = 
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Date_time required]
VAR vColumn2 = bw_Cofiwerkorders[Date_time finished]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime =
    MIN ( vColumn1, vColumn2 ) 
// the later of the two columns
VAR vEndDatetime =
    MAX ( vColumn1, vColumn2 )
VAR vStartDate =
    INT ( vStartDatetime )
VAR vEndDate =
    INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
    CALCULATE (
        COUNTROWS ( DimDate ),
        DimDate[Date] > vStartDate,
        DimDate[Date] < vEndDate,
        DimDate[Weekday Flag] = 1
    )
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date end of day
VAR vStartDateEOD =
    vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD =
    vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
VAR vStartDateSeconds =
    DATEDIFF ( vStartDatetime, vStartDateEOD, SECOND )
VAR vEndDateSeconds =
    DATEDIFF ( vEndDateBOD, vEndDatetime, SECOND )
VAR vTotalWorkdaySeconds = vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
VAR vResult =
    IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
    vResult

If you want to format the result as hh:mm:ss, see the article below:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

[Anonymous]

Hi @DataInsights 

Thank you, the formula works great but I found a few problems.
1: If date_time required and date_time finished are on the same day/date then the formula adds automatically 32400 seconds(1 workday) to the result. This is the main issue.

And if it is possible I would like to fix the two following problems:
1: When one of the date/times is 00:00:00 then the formula does -28800.
2: When one of the times is noted outside of the working hours, for example 17:22:00, then the formule subtracts 22 minutes/1320 seconds from the total result. I would like to see that the formula ignores that 22 minutes and sees it like 17:00:00 or 8:00:00.

[DataInsights · Accepted solution]

@Anonymous,

Try this calculated column. You can expand the SWITCH functions in variables vStartDatetimeAdj and vEndDatetimeAdj to handle additional scenarios (e.g., midnight).

Workday Seconds = 
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Date_time required]
VAR vColumn2 = bw_Cofiwerkorders[Date_time finished]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime = MIN ( vColumn1, vColumn2 ) 
// the later of the two columns
VAR vEndDatetime = MAX ( vColumn1, vColumn2 )
VAR vStartDate = INT ( vStartDatetime )
VAR vEndDate = INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
    CALCULATE (
        COUNTROWS ( DimDate ),
        DimDate[Date] > vStartDate,
        DimDate[Date] < vEndDate,
        DimDate[Weekday Flag] = 1
    )
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date beginning of day
VAR vStartDateBOD = vStartDate + TIME ( vWorkdayStartHour, 0, 0 )
// Start Date end of day
VAR vStartDateEOD = vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD = vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
// End Date end of day
VAR vEndDateEOD = vEndDate + TIME ( vWorkdayEndHour, 0, 0 )
VAR vStartDatetimeAdj = 
    SWITCH ( TRUE (),
        vStartDatetime < vStartDateBOD, vStartDateBOD,
        vStartDatetime > vStartDateEOD, vStartDateEOD,
        vStartDatetime
    )
VAR vEndDatetimeAdj = 
    SWITCH ( TRUE (),
        vEndDatetime < vEndDateBOD, vEndDateBOD,
        vEndDatetime > vEndDateEOD, vEndDateEOD,
        vEndDatetime
    )
VAR vStartDateSeconds = DATEDIFF ( vStartDatetimeAdj, vStartDateEOD, SECOND )
VAR vEndDateSeconds = DATEDIFF ( vEndDateBOD, vEndDatetimeAdj, SECOND )
VAR vTotalWorkdaySeconds =
    SWITCH ( TRUE (),
        vStartDate = vEndDate, DATEDIFF ( vStartDatetimeAdj, vEndDatetimeAdj, SECOND ),
        vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
    )
VAR vResult = IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
    vResult