Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 ho...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    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