Calculating working days between two dates on different rows

[GeorgeR1]

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.

[v-cazheng-msft · Accepted solution]

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!

[amitchandak]

@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))