Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
GeorgeR1
Frequent Visitor

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 IDMilestone TypeMilestone End Date
1234561. Background Research01/12/2021
1234562. Terms of Reference03/02/2022
1234563. Opening Meeting04/03/2022
1234564. Fieldwork and Closing Meeting21/03/2022
1234565. Draft Report25/03/2022
1234566. Management Responses29/03/2022
1234567. Final Report07/04/2021
2345671. Background Research01/01/2022
2345672. Terms of Reference03/01/2022
2345673. Opening Meeting04/02/2022
2345674. Fieldwork and Closing Meeting12/02/2022
3456781. Background Research01/01/2021
3456782. Terms of Reference23/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.

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1642405424076.png

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!

 

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1642405424076.png

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
Super User
Super User

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

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.