Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |