Forum Discussion
Calculate hours difference between two dates (excluding weekend)
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:
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 * 24RETURNday3Wish 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