Forum Discussion
Solved
DAX Count working days between two dates
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
4 Replies
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
