Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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!
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!
@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))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |