Forum Discussion
PAS - Date Between Two Weeks
Hello.
I have a series of activities that each have a start date and a finish date. I would like to set up a PAS (performance against schedule) with this data that will calculate the number of 'on time' and 'early' over the number of 'misses' to give me a percentage. I would also like to view a table of the activities. For this example, we will say the date range 3 weeks ago is 3/5-3/11 and 2 weeks ago is 3/12-3/18.
The week should always be a week, Sunday to Saturday.
I only want to calculate activities that were scheduled to start or finish in the next week, 3 weeks ago. So, in week 8, what was scheduled to start or finish in week 9? Both the start and the finish can count as 'on time', 'early', and 'miss'.
Currently, I have the below table calculated. I am struggling to isolate (filter?) the activities that I would like to see. I thought I could use power querry and use =Date.IsInNextWeek([Start]) but that is a rolling date based on today. I need something that will look at dates in between. I do have a date table with the date range of each week. My data is locked in each Wednesday.
Help, please?
| Activity ID | Activity Name | Start 3 Weeks Ago | Start 2 Weeks Ago | Finish 3 Weeks Ago | Finish 2 Weeks Ago | Start Delta | Finish Delta | Start PAS | Finish PAS | |||
| 4834 | get permits | 3/6/2023 | 3/6/2023 | 3/7/2023 | 3/7/2023 | 0 | 0 | On Time | On Time | |||
| 9843 | rough grade | 3/7/2023 | 3/7/2023 | 3/8/2023 | 3/8/2023 | 0 | 0 | On Time | On Time | |||
| 9832 | grade | 3/8/2023 | 3/7/2023 | 3/9/2023 | 3/8/2023 | -1 | -1 | Early | Early | Total On Time or Early | 6 | |
| 9821 | compress | 3/9/2023 | 3/8/2023 | 3/10/2023 | 3/9/2023 | -1 | -1 | Late | Late | Total Activities | 8 | |
| 9810 | frp | 3/13/2023 | 3/12/2023 | 3/14/2023 | 3/13/2023 | -1 | -1 | Early | Early | 75% | ||
| 9799 | cure | 3/14/2023 | 3/18/2023 | 3/15/2023 | 3/19/2023 | 4 | 4 | Late | Late | |||
| 9788 | frame | 3/18/2023 | 3/19/2023 | 3/19/2023 | 3/20/2023 | 1 | 1 | Late | Late | |||
| 9777 | drywall | 3/19/2023 | 3/20/2023 | 3/20/2023 | 3/21/2023 | 1 | 1 | Late | Late | |||
| 9766 | tfm | 3/20/2023 | 3/21/2023 | 3/21/2023 | 3/22/2023 | 1 | 1 | Late | Late | |||
| 9755 | electrical | 3/21/2023 | 3/22/2023 | 3/22/2023 | 3/23/2023 | 1 | 1 | Late | Late | |||
| 9744 | plumbing | 3/22/2023 | 3/25/2023 | 3/23/2023 | 3/26/2023 | 3 | 3 | Late | Late |
4 Replies
why is 9821 classified as late?
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
The results that I would like to achieve are the isolation of activities 4834, 9843, 9832 and 9821, a count of the total early and on time, a count of the total activities, and the PAS score resulting from those, which is count of early and on time over total as a percentage, PAS.
Activity ID Activity Name Start 3 Weeks Ago Start 2 Weeks Ago Finish 3 Weeks Ago Finish 2 Weeks Ago Start Delta Finish Delta Start PAS Finish PAS Total On Time or Early 7 4834 get permits 3/6/2023 3/6/2023 3/7/2023 3/7/2023 0 0 On Time On Time Total Activities 8 9843 rough grade 3/7/2023 3/7/2023 3/8/2023 3/8/2023 0 0 On Time On Time PAS 88% 9832 grade 3/8/2023 3/7/2023 3/9/2023 3/8/2023 -1 -1 Early Early 9821 compress 3/9/2023 3/10/2023 3/10/2023 3/11/2023 1 1 Late Early 9810 frp 3/13/2023 3/12/2023 3/14/2023 3/13/2023 -1 -1 Early Early 9799 cure 3/14/2023 3/18/2023 3/15/2023 3/19/2023 4 4 Late Late 9788 frame 3/18/2023 3/19/2023 3/19/2023 3/20/2023 1 1 Late Late 9777 drywall 3/19/2023 3/20/2023 3/20/2023 3/21/2023 1 1 Late Late 9766 tfm 3/20/2023 3/21/2023 3/21/2023 3/22/2023 1 1 Late Late 9755 electrical 3/21/2023 3/22/2023 3/22/2023 3/23/2023 1 1 Late Late 9744 plumbing 3/22/2023 3/25/2023 3/23/2023 3/26/2023 3 3 Late Late
