topic Re: DAX Count working days between two dates in DAX Commands and Tips

DAX Count working days between two dates

jimbob2285 — Fri, 28 Jul 2023 13:38:52 GMT

I've come across this DAX code to count the working days between two dates, that uses my date table to define if a day is a working day or not

Days Overdue =

CALCULATE(

COUNTROWS ('Date'),

DATESBETWEEN ('Date'[Date], Fact[Due], Fact[Completed]),

'Date'[WD] = "Yes",

ALL('Fact')

) - 1

But it seems to be struggling with negative days, i.e. if the Completed date is before the Due date

I've modified the formula, as below, to swap the two dates around if the completed date is before the due date:

Days Overdue =

IF(Fact[Completed] < Fact[Due],

(CALCULATE(

COUNTROWS ('Date'),

DATESBETWEEN ('Date'[Date], Fact[Completed], Fact[Due]),

'Date'[WD] = "Yes",

ALL('Fact')

) - 1) * -1,

CALCULATE(

COUNTROWS ('Date'),

DATESBETWEEN ('Date'[Date], Fact[Due], Fact[Completed]),

'Date'[WD] = "Yes",

ALL('Fact')

) - 1

But I just wanted to check this is the correct approach as it seems more of a work around than a solution, and to understand why the first formula struggles to go back in time

Thanks

Jim

Re: DAX Count working days between two dates

Anonymous — Fri, 28 Jul 2023 18:06:56 GMT

can you use the NETWORKDAYS function ?

Working Days Test =

var _StartDate= 2023-07-28

var _EndDate = 2023-07-25

return

NETWORKDAYS(_EndDate,_StartDate,1)

Re: DAX Count working days between two dates

jimbob2285 — Mon, 31 Jul 2023 16:37:11 GMT

Hi Ikhall

Thanks, Yes, NETWORKDAYS certainly overcomes the problem of negative days, calculating them correctly, but is there a way to reference a holiday table do you know? It will be a bit long winded to add each holiday for a single year, with bank holidays and Christams shut down, let alone all those holidays for a number of years

Cheers

Jim

Re: DAX Count working days between two dates

Anonymous — Mon, 31 Jul 2023 17:43:05 GMT

what are you using for a date dimension table currently ?

Re: DAX Count working days between two dates

jimbob2285 — Tue, 01 Aug 2023 08:08:32 GMT

I've built my own date table in SQL