Forum Discussion

BigKev's avatar
BigKev
Helper III
5 years ago
Solved

Calculate hours difference between two dates (excluding weekend)

Hi All,   Does anyone know how I can calculate in dax the hours difference between two dates/ time columns (excluding weekend dates)? I have created a (T/F) flag in my master calendar to flag if a...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi BigKev ,

    You cold try  the following  Steps:

    Step 1:create date table:

    Dates = CALENDAR("2021/1/1","2021/12/31")

     

     

    Step 2:create new table base on dates:

    date1 =
    GENERATE (
        CALENDARAUTO (),
        VAR yyyy =
            YEAR ( [Date] )
        VAR mmmm =
            MONTH ( [Date] )
        RETURN
            ROW (
                "Year", yyyy,
                "Month", FORMAT ( [Date], "mmmm" ),
                "Month2", mmmm,
                "Week1", FORMAT ( [Date], "dddd" ),
                "Week2", WEEKDAY ( [Date] ),
                "weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 }
            )
    )

     

     

    Step 3, Create new column in main database table:

    difforder1 =
    VAR day1 =
    CALCULATE (
    COUNTROWS ( 'date1' ),
    DATESBETWEEN ( date1[Date], Abc[Start Date], Abc[End Date]),
    date1[weekday] <> TRUE,
    ALL ( Abc )
    )
    VAR day2 =
    IF ( day1 = BLANK (), 0, day1 )
    VAR day3 =
    DATEDIFF ( Abc[Start Date], Abc[End Date], HOUR ) - day2 * 24
    RETURN
    day3

     

     

     

     

     

    Wish it is helpful for you!

     

    Best Regard

    Lucien Wang

BigKev's avatar
BigKev
Helper III
5 years ago

Hi amitchandak , 

 

Thanks for the prompt response.

 

I don't need to consider business hours. I just need to calculate the number of hours between two dates (as per my data above), excluding the weekend date.

 

For example,

 

Start Date                    End Date                        No. of Hours 

18/03/2021 8:00        19/03/2021 10:00                26

18/03/2021 8:00        22/03/2021 17:00                55

 

As you can see in row one, the two dates are working dates. Hence the hour's diff between the two dates is 26 hours.

In Row two, we don't consider 20/03 & 21/03 because it is Sat & Sun. Hence, the hour's diff is 55 hours.

 

do you know how I can calculate these hours diff?

 

 

Cheers,

Kev