Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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))
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |