Forum Discussion
Calculating working days between two dates on different rows
I am trying to create a column that finds the number of workingdays (excluding weekends and bankholidays - I have a list of all bank holidays in a table) between Milestones but the Audit Review ID must be the same. I will need to calculate the difference between the End Date of Milestone 1 with the End Date with Milestone 2 and so on... Not all Audit Reviews have an End Date for all 7 milestones, some only have a few as seen in example e.g. Audit 345678 only has the first 2 milestones.
| Audit Review ID | Milestone Type | Milestone End Date |
| 123456 | 1. Background Research | 01/12/2021 |
| 123456 | 2. Terms of Reference | 03/02/2022 |
| 123456 | 3. Opening Meeting | 04/03/2022 |
| 123456 | 4. Fieldwork and Closing Meeting | 21/03/2022 |
| 123456 | 5. Draft Report | 25/03/2022 |
| 123456 | 6. Management Responses | 29/03/2022 |
| 123456 | 7. Final Report | 07/04/2021 |
| 234567 | 1. Background Research | 01/01/2022 |
| 234567 | 2. Terms of Reference | 03/01/2022 |
| 234567 | 3. Opening Meeting | 04/02/2022 |
| 234567 | 4. Fieldwork and Closing Meeting | 12/02/2022 |
| 345678 | 1. Background Research | 01/01/2021 |
| 345678 | 2. Terms of Reference | 23/01/2022 |
For example, I would expect a value of 5 for the difference between (4. Fieldwork & Closing Meeting) and (5. Draft Report) for Audit 123456.
Would apprecaite any help.
Hi GeorgeR1
According to your description, if you expect a value of 5 for the difference between 4 and 5 for Audit 123456. You can try this Calculated column.
MilestoneDiff =
VAR _lastdate =
MAXX (
FILTER (
'Table',
'Table'[Audit Review ID] = EARLIER ( [Audit Review ID] )
&& 'Table'[Milestone Type] < EARLIER ( [Milestone Type] )
),
'Table'[Milestone End Date]
)
VAR internal =
DATEDIFF ( _lastdate, 'Table'[Milestone End Date], DAY )
RETURN
IF ( ISBLANK ( internal ), BLANK (), internal + 1 )
And the result should look like this.
As you can see, there is a negative value, which I’m a little bit confused. Is the date 4/7/2021 a precise data or just a misspell? In addition, there is a weekend between 3/21/2022(4. Fieldwork & Closing Meeting) and 3/25/2022(5. Draft Report) for Audit 123456, if you would like to exclude weekends, the difference value seems to be 3 but not 5. That’s what I’m feeling unsure about.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
2 Replies
GeorgeR1 , new column for working days
Var _lastdate = maxx(filter(Table, [Audit Review ID] = earlier([Audit Review ID]) && [Milestone End Date] < earlier([Milestone End Date])),[Milestone End Date])
return
COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_lastdate,[Milestone End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))Hi GeorgeR1
According to your description, if you expect a value of 5 for the difference between 4 and 5 for Audit 123456. You can try this Calculated column.
MilestoneDiff =
VAR _lastdate =
MAXX (
FILTER (
'Table',
'Table'[Audit Review ID] = EARLIER ( [Audit Review ID] )
&& 'Table'[Milestone Type] < EARLIER ( [Milestone Type] )
),
'Table'[Milestone End Date]
)
VAR internal =
DATEDIFF ( _lastdate, 'Table'[Milestone End Date], DAY )
RETURN
IF ( ISBLANK ( internal ), BLANK (), internal + 1 )
And the result should look like this.
As you can see, there is a negative value, which I’m a little bit confused. Is the date 4/7/2021 a precise data or just a misspell? In addition, there is a weekend between 3/21/2022(4. Fieldwork & Closing Meeting) and 3/25/2022(5. Draft Report) for Audit 123456, if you would like to exclude weekends, the difference value seems to be 3 but not 5. That’s what I’m feeling unsure about.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
