Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 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 |
Solved! Go to Solution.
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:
Wish it is helpful for you!
Best Regard
Lucien Wang
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:
Wish it is helpful for you!
Best Regard
Lucien Wang
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
@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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |