Forum Discussion

BigKev's avatar
BigKev
Helper III
5 years ago
Solved

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...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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 * 24
    RETURN
    day3

     

     

     

     

     

    Wish it is helpful for you!

     

    Best Regard

    Lucien Wang

amitchandak's avatar
amitchandak
Super User
5 years ago

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)