DAX Count working days between two dates

[jimbob2285]

Hi

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

[lkhall · Accepted solution]

can you use the NETWORKDAYS function ?

Working Days Test =

var _StartDate= 2023-07-28

var _EndDate = 2023-07-25

return

NETWORKDAYS(_EndDate,_StartDate,1)

[jimbob2285]

Hi

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

[lkhall]

what are you using for a date dimension table currently ? 

[jimbob2285]

I've built my own date table in SQL