Forum Discussion

dcs136's avatar
dcs136
Icon for Advocate I rankAdvocate I
10 years ago
Solved

Calculate Date and Time difference considering the weekends and workhours

  Hi everybody!   I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer! Here is the thing,   I need to calculate the difference between dates and tim...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    dcs136

     

    The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

    If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

     

     

    Best Regards,

    Herbert

     

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    dcs136

     

    How about the result if we update the “FirstDaySecDiff” measure as below?

     

    FirstDaySecDiff = 
IF (
    Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
        && Table1[FirstDayEndTime] <= Table1[DateTimeTo],
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
    IF (
        Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
            && Table1[FirstDayEndTime] > Table1[DateTimeTo],
        DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
        0
    )
)

     

    Best Regards,

    Herbert

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

dcs136

 

There may be several methods to get the expected result. I’ll divide the difference to three parts, the first day, the middle days and the last day. For details, please refer to following steps.

I’ve also upload my .pbix file here for reference.

 

  1. Create a calendar table with following formula. But do not create relationship between these two tables.
    Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )
  2. Create a column in calendar table to mark the working days.
    WorkDay = 
VAR WeekDayNum =
    WEEKDAY ( 'Calendar'[Date], 2 )
RETURN
    (
        IF ( WeekDayNum = 6 || WeekDayNum = 7, FALSE (), TRUE () )
    )
  3. Create a column to store the working end time of first day.
    FirstDayEndTime = 
DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
& " 18:00:00"
  4. Create a column to calculate the working seconds of first day.
    FirstDaySecDiff = 
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )
  5. Create a column to store the working start time of last day.
    LastDayStartTime = 
DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
& " 8:00:00"
  6. Create a column to store the working end time of last day.
    LastDayEndTime = 
DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
& " 18:00:00"
  7. Create a column to calculate the working seconds of last day.
    LastDaySecDiff = 
IF (
    FORMAT ( Table1[DateTimeFrom], "Short Date" )
        <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
    IF (
        Table1[DateTimeTo] >= Table1[LastDayStartTime]
            && Table1[DateTimeTo] <= Table1[LastDayEndTime],
        DATEDIFF ( Table1[LastDayStartTime], Table1[DateTimeTo], SECOND ),
        IF (
            Table1[DateTimeTo] > Table1[LastDayEndTime],
            DATEDIFF ( Table1[LastDayStartTime], Table1[LastDayEndTime], SECOND ),
            0
        )
    ),
    0
)
  8. Create a column to calculate the working seconds of middle days.
    MidDaysSecDiff = 
IF (
    FORMAT ( Table1[DateTimeFrom], "Short Date" )
        <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calendar'[Date] ),
                FILTER (
                    'Calendar',
                    'Calendar'[Date] > Table1[FirstDayEndTime]
                        && 'Calendar'[Date] < Table1[LastDayStartTime]
                        && 'Calendar'[WorkDay] = TRUE ()
                )
            )
                - 1
        ),
    0
)
  9. Create the final column to calculate the total working hours.
    TotalHourDiff = 
 ( Table1[FirstDaySecDiff] + Table1[LastDaySecDiff]
    + Table1[MidDaysSecDiff] )
/ 3600

Note: The data type of columns of “FirstDayEndTime”, “LastDayStartTime” and “LastDayEndTime” should be Date/Time as below.

 

Best Regards,

Herbert