topic Re: Dax query that calculate InWork hours excluding weakeneds in DAX Commands and Tips

Dax query that calculate InWork hours excluding weakeneds

nony97 — Thu, 10 Oct 2024 12:37:41 GMT

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

Re: Dax query that calculate InWork hours excluding weakeneds

SachinNandanwar — Thu, 10 Oct 2024 15:41:29 GMT

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

Re: Dax query that calculate InWork hours excluding weakeneds

nony97 — Thu, 10 Oct 2024 15:42:29 GMT

No unfortunately

Re: Dax query that calculate InWork hours excluding weakeneds

SachinNandanwar — Thu, 10 Oct 2024 15:46:48 GMT

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? )

Re: Dax query that calculate InWork hours excluding weakeneds

nony97 — Thu, 10 Oct 2024 16:36:06 GMT

What is the next step after creating it?

Re: Dax query that calculate InWork hours excluding weakeneds

SachinNandanwar — Thu, 10 Oct 2024 16:52:40 GMT

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

Re: Dax query that calculate InWork hours excluding weakeneds

nony97 — Thu, 10 Oct 2024 16:52:10 GMT

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

Re: Dax query that calculate InWork hours excluding weakeneds

SachinNandanwar — Thu, 10 Oct 2024 16:54:16 GMT

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

Re: Dax query that calculate InWork hours excluding weakeneds

Anonymous — Fri, 11 Oct 2024 06:09:45 GMT

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.

Re: Dax query that calculate InWork hours excluding weakeneds

nony97 — Thu, 24 Oct 2024 04:07:02 GMT

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