cancel
Showing results for
Did you mean:

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

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.

Thanks all.

Cheers,

Kev

 Start Date End Date 17/03/2021 17:30 18/03/2021 16:45 18/03/2021 17:00 20/03/2021 16:03 19/03/2021 17:00 22/03/2021 16:00 17/03/2021 10:00 18/03/2021 16:00 18/03/2021 17:00 20/03/2021 16:00 19/03/2021 17:33 22/03/2021 16:50 17/03/2021 8:00 17/03/2021 8:00 18/03/2021 15:00 20/03/2021 16:00 19/03/2021 17:00 22/03/2021 16:55 17/03/2021 17:00 18/03/2021 17:00 18/03/2021 17:00 20/03/2021 16:00 19/03/2021 17:00 22/03/2021 21:00
1 ACCEPTED SOLUTION
Community Support

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

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

3 REPLIES 3
Community Support

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

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

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

Super User

@BigKev , If you need business hours

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)

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors