Remaining working days in a date interval

Question

Suppose that i have a table, in which I want to make a column where I count the remaining workdays in a date interval with respect to the current date. An example of such may look like this:

 Id      Date         Start         End    Remaining
+---+------------+------------+------------+-----+
| 1 | 01-01-2020 | 01-01-2020 | 31-12-2020 | 262 |
+---+------------+------------+------------+-----+
| 1 | 02-01-2020 | 01-01-2020 | 31-12-2020 | 261 |
+---+------------+------------+------------+-----+
| 1 | 03-01-2020 | 01-01-2020 | 31-12-2020 | 260 |
+---+------------+------------+------------+-----+
| 1 | 04-01-2020 | 01-01-2020 | 31-12-2020 | 260 | <--- Weekend
+---+------------+------------+------------+-----+
| 1 | 05-01-2020 | 01-01-2020 | 31-12-2020 | 260 | <--- Weekend
+---+------------+------------+------------+-----+
| 1 | 06-01-2020 | 01-01-2020 | 31-12-2020 | 259 |
+---+------------+------------+------------+-----+
| 1 | 07-01-2020 | 01-01-2020 | 31-12-2020 | 258 |
+---+------------+------------+------------+-----+

Here, I have a date column and the respective start/end dates. As you can see, optimally, I want the countdown to stop, whenever I hit a weekend, similar to the above example.

Additionally, I want to do it in a way, so that I respect individual Ids... That is, I have different Ids with different start and end dates, further down the table.

Currently, I have a hard time figuring out how to make the countdown stop. As of now, I have tried the following DAX formula:

Sum_without_weekends = CALCULATE(COUNTROWS('Table'),
FILTER('Table'(SWITCH(WEEKDAY([Date]),1,0,7,0,1) <> 0) && ('Table'[Id]=EARLIER('Table'[Id])))) 
+ IF((SWITCH(WEEKDAY('Table'[Date]),1,0,7,0,1) <> 0),DATEDIFF('Table'[Date],'Table'[Start].[Date],DAY), 0)

This does however yield something along the lines of this, which is wrong:

 Id      Date       StartDate    EndDate   Remaining
+---+------------+------------+------------+-----+
| 1 | 01-01-2020 | 01-01-2020 | 31-12-2020 | 262 |
+---+------------+------------+------------+-----+
| 1 | 02-01-2020 | 01-01-2020 | 31-12-2020 | 261 |
+---+------------+------------+------------+-----+
| 1 | 03-01-2020 | 01-01-2020 | 31-12-2020 | 260 |
+---+------------+------------+------------+-----+
| 1 | 04-01-2020 | 01-01-2020 | 31-12-2020 | 262 | <--- Weekend
+---+------------+------------+------------+-----+
| 1 | 05-01-2020 | 01-01-2020 | 31-12-2020 | 262 | <--- Weekend
+---+------------+------------+------------+-----+
| 1 | 06-01-2020 | 01-01-2020 | 31-12-2020 | 257 |
+---+------------+------------+------------+-----+
| 1 | 07-01-2020 | 01-01-2020 | 31-12-2020 | 256 |
+---+------------+------------+------------+-----+

Therefore, I need help finishing the DAX formula, in order to make the countdown stop when I hit a weekend, similar to what is visualized in the first table.

CR7SMS CR7SMS · Accepted Answer · 2020-02-06T16:12:09

Perhaps something like this:

Remaining Working Days = 
VAR ID1 = Table1[ID]
VAR Date1 = Table1[Date]
VAR Work_Days = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[ID]=ID1),FILTER(Table1,WEEKDAY(Table1[Date]) in {2,3,4,5,6}),FILTER(Table1,Table1[Date]>=Date1))
RETURN Work_Days

The above calculation uses just the "Date" variable to get the desired result. The idea is to count all working days after the current date and applying a filter for ID as well. Hope this helps.

Remaining working days in a date interval

1 Answers