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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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