Dax query that calculate InWork hours excluding weakeneds

[nony97]

i wrote the following dax query that calculate work hourse 8am to 5 pm however i need it to exclude the weekend (exclude saturday and friyday) please help 

for example this is how it currenlty is being calculatined

start date	end date	work hourse
4/22/2022 3:00	4/23/2022 14:04:00	15

 this is how i want it to be 

start date	end date	work hourse
4/22/2022 3:00	4/23/2022 14:04:00	0

and here is the query 

workhours new = 
-- Working Start and End time 
VAR WorkTimeStart = TIME ( 08, 00, 00 )
VAR WorkTimeEnd = TIME ( 17, 10, 10 )
VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )

-- Start and End date/time on current row
VAR StartingDateTime = [start date]
VAR EndingDateTime   = [end date]

VAR StartingTime= StartingDateTime - TRUNC ( StartingDateTime )
VAR StartingDate = StartingDateTime - StartingTime

VAR EndingTime = EndingDateTime - TRUNC ( EndingDateTime )
VAR EndingDate = EndingDateTime - EndingTime

-- Adjust start/end times to fall within working hours.
VAR StartingTimeEffective =
    MIN (
        MAX ( StartingTime, WorkTimeStart ),
        WorkTimeEnd
    )
VAR EndingTimeEffective =
    MAX (
        MIN ( EndingTime, WorkTimeEnd ),
        WorkTimeStart
    )
-- Adjust for hours not worked on StartingDate
--   StartingTimeOffset will always be <= 0
VAR StartingTimeOffset =
    WorkTimeStart - StartingTimeEffective
-- Adjust for hours not worked on EndingDate
--   EndingTimeOffset will always be <= 0
VAR EndingTimeOffset =
    EndingTimeEffective - WorkTimeEnd
VAR DayCount =
    EndingDate - StartingDate + 1
VAR TotalTimeInDays =
    DayCount * WorkingHours + StartingTimeOffset + EndingTimeOffset
VAR TotalTimeInHours =
    TotalTimeInDays * 24

RETURN
    TotalTimeInHours

[v-yilong-msft · Accepted solution]

Hi @nony97 ,

I create a table as you mentioned.

Then I think you can use this DAX code.

WorkHours =
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
VAR WorkingHoursPerDay = 9
VAR StartTime =
    TIME ( 8, 0, 0 )
VAR EndTime =
    TIME ( 17, 0, 0 )
VAR TotalDays =
    DATEDIFF ( StartDate, EndDate, DAY )
VAR Weekdays =
    FILTER (
        GENERATE (
            CALENDAR ( StartDate, EndDate ),
            VAR CurrentDay = [Date]
            RETURN
                ROW (
                    "Day", CurrentDay,
                    "IsWeekday", IF ( WEEKDAY ( CurrentDay, 2 ) IN { 5, 6 }, FALSE, TRUE )
                )
        ),
        [IsWeekday] = TRUE
    )
VAR TotalWeekdays =
    COUNTROWS ( Weekdays )
VAR TotalWorkHours =
    IF (
        TotalDays = 0,
        IF (
            WEEKDAY ( StartDate, 2 )
                IN { 5, 6 }
                    || WEEKDAY ( EndDate, 2 ) IN { 5, 6 },
            0,
            DATEDIFF ( StartDate, EndDate, HOUR )
        ),
        TotalWeekdays * WorkingHoursPerDay
    )
RETURN
    IF ( ISBLANK ( TotalWorkHours ), 0, TotalWorkHours )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nony97]

thank you 

but the calculation is not 100% accurate can you plwasw help 

for wxample 

start	ebd	cuurent result (Hours)	Desired result(Hours)
04/06/2024	04/06/2024	2.00	1.19

[SachinNandanwar]

Do you have a seperate date table that defines the weekends ?

[nony97]

No unfortunately 

[SachinNandanwar]

I think its better that you create one.It would make a lot of things much simpler.

DimDate = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),   -- Define the date range
    "Year", YEAR([Date]),                              -- Year
    "Month Number", MONTH([Date]),                     -- Month Number (1 to 12)
    "Month Name", FORMAT([Date], "MMMM"),              -- Full Month Name (January, February, etc.)
    "Day", DAY([Date]),                                -- Day of the month
    "Weekday Number", WEEKDAY([Date], 2),              -- Weekday Number (1 = Monday, 7 = Sunday)
    "Weekday Name", FORMAT([Date], "dddd"),            -- Full Weekday Name (Monday, Tuesday, etc.)
    "IsWeekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE()), -- Weekend (True/False)
    "Quarter", QUARTER([Date]),                        -- Quarter (1 to 4)
    "Quarter Name", "Q" & QUARTER([Date]),             -- Quarter Name (Q1, Q2, etc.)
    "Year-Quarter", YEAR([Date]) & "-Q" & QUARTER([Date]), -- Year and Quarter (2020-Q1, etc.)
    "Week Number", WEEKNUM([Date], 2),                 -- Week Number of the year
    "Year-Month", FORMAT([Date], "YYYY-MM"),           -- Year and Month (2020-01, etc.)
    "IsLeapYear", IF(MOD(YEAR([Date]), 4) = 0 && (MOD(YEAR([Date]), 100) <> 0 || MOD(YEAR([Date]), 400) = 0), TRUE(), FALSE()) -- Is it a Leap Year?
)

[nony97]

What is the next step after creating it? 

[SachinNandanwar]

Ignore the data in your calculation that are marked as weekends in the date table.

[nony97]

But what if the 

request came on Saturday at 5 and ended on Sunday, it will calculate the workhours in Saturday which is not what i want

[SachinNandanwar]

If saturday is marked as weekend in your date table then your calculation should ignore it.