Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BigKev
Helper III
Helper III

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 date is a weekend or not. So it just a matter to join both of them together.

 

Please find below my data.

 

Thanks all.

 

Cheers,

Kev

 

Start DateEnd Date
17/03/2021 17:3018/03/2021 16:45
18/03/2021 17:0020/03/2021 16:03
19/03/2021 17:0022/03/2021 16:00
17/03/2021 10:0018/03/2021 16:00
18/03/2021 17:0020/03/2021 16:00
19/03/2021 17:3322/03/2021 16:50
17/03/2021 8:0017/03/2021 8:00
18/03/2021 15:0020/03/2021 16:00
19/03/2021 17:0022/03/2021 16:55
17/03/2021 17:0018/03/2021 17:00
18/03/2021 17:0020/03/2021 16:00
19/03/2021 17:0022/03/2021 21:00
1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @BigKev ,

You cold try  the following  Steps:

Step 1:create date table:

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

v-luwang-msft_0-1616403503959.png

 

 

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 { 17 }
        )
)

v-luwang-msft_1-1616403503967.png

 

 

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

v-luwang-msft_3-1616403669961.png

 

 

 

 

 

Wish it is helpful for you!

 

Best Regard

Lucien Wang

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @BigKev ,

You cold try  the following  Steps:

Step 1:create date table:

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

v-luwang-msft_0-1616403503959.png

 

 

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 { 17 }
        )
)

v-luwang-msft_1-1616403503967.png

 

 

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

v-luwang-msft_3-1616403669961.png

 

 

 

 

 

Wish it is helpful for you!

 

Best Regard

Lucien Wang

BigKev
Helper III
Helper III

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

amitchandak
Super User
Super User

@BigKev , If you need business hours

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

 

ot try a measure like

 

Work hours =
var _dt1 = date(year(Table[Start Date]),month(Table[Start Date]),day(Table[Start Date]))
var _dt2 = date(year(Table[End Date]),month(Table[End Date]),day(Table[End Date]))
return
if(weekday(_dt1,2) <6 , datediff(Table[Start Date],_dt1+1,hours),0) + COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_dt1+1,_dt2-1),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))*24
+ if( not(weekday(_dt2,2) in {6,7}) , datediff(dt_2 -1 , Table[End Date],hours),0)

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.