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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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)

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.