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