Forum Discussion
Solved
DAX Measure for Open Issues Previous Week
My data source has a list of issues - each with an open date and a close date. I have (with the help of others) created a DAX measure that returns the number of issues open in a week, ie 2023 week 51...
Here's the general approach for these patterns. Needs some refinement as some of the rules are open to interpretation. Your week numbers were off too.
Thank you. PLease also indicate YearWeek numbers and add sample data where issues were opened in December 2023.
lbendlin I have added some data from 2024 and some columns from the date table.
| ID | Date Open | Date Closed | Department | End of Week | Week Number | CurrWeekOffset |
| 32 | 03/12/2023 | Department 9 | 03/12/2023 | 48 | -16 | |
| 31 | 07/12/2023 | Department 6 | 10/12/2023 | 49 | -15 | |
| 27 | 19/12/2023 | 27/02/2024 | Department 7 | 24/12/2023 | 51 | -13 |
| 28 | 19/12/2023 | 28/02/2024 | Department 2 | 24/12/2023 | 51 | -13 |
| 29 | 19/12/2023 | 01/03/2024 | Department 2 | 24/12/2023 | 51 | -13 |
| 30 | 19/12/2023 | 01/03/2024 | Department 6 | 24/12/2023 | 51 | -13 |
| 21 | 24/12/2023 | 04/03/2024 | Department 6 | 24/12/2023 | 51 | -13 |
| 22 | 24/12/2023 | 04/03/2024 | Department 6 | 24/12/2023 | 51 | -13 |
| 23 | 24/12/2023 | 24/12/2023 | Department 6 | 24/12/2023 | 51 | -13 |
| 24 | 24/12/2023 | 24/12/2023 | Department 6 | 24/12/2023 | 51 | -13 |
| 25 | 24/12/2023 | Department 6 | 24/12/2023 | 51 | -13 | |
| 26 | 24/12/2023 | Department 6 | 24/12/2023 | 51 | -13 | |
| 14 | 03/01/2024 | 09/01/2024 | Department 4 | 07/01/2024 | 1 | -11 |
| 15 | 03/01/2024 | 11/01/2024 | Department 10 | 07/01/2024 | 1 | -11 |
| 16 | 03/01/2024 | 17/01/2024 | Department 5 | 07/01/2024 | 1 | -11 |
| 17 | 03/01/2024 | 14/02/2024 | Department 8 | 07/01/2024 | 1 | -11 |
| 18 | 03/01/2024 | 21/02/2024 | Department 5 | 07/01/2024 | 1 | -11 |
| 19 | 03/01/2024 | Department 3 | 07/01/2024 | 1 | -11 | |
| 20 | 03/01/2024 | Department 7 | 07/01/2024 | 1 | -11 | |
| 13 | 08/01/2024 | 25/01/2024 | Department 1 | 14/01/2024 | 2 | -10 |
| 8 | 19/01/2024 | 27/02/2024 | Department 7 | 21/01/2024 | 3 | -9 |
| 9 | 19/01/2024 | Department 2 | 21/01/2024 | 3 | -9 | |
| 10 | 19/01/2024 | Department 2 | 21/01/2024 | 3 | -9 | |
| 11 | 19/01/2024 | Department 6 | 21/01/2024 | 3 | -9 | |
| 12 | 19/01/2024 | Department 8 | 21/01/2024 | 3 | -9 | |
| 2 | 24/01/2024 | 04/03/2024 | Department 6 | 28/01/2024 | 4 | -8 |
| 3 | 24/01/2024 | 04/03/2024 | Department 6 | 28/01/2024 | 4 | -8 |
| 4 | 24/01/2024 | 04/03/2024 | Department 6 | 28/01/2024 | 4 | -8 |
| 5 | 24/01/2024 | 04/03/2024 | Department 6 | 28/01/2024 | 4 | -8 |
| 6 | 24/01/2024 | Department 6 | 28/01/2024 | 4 | -8 | |
| 7 | 24/01/2024 | Department 6 | 28/01/2024 | 4 | -8 | |
| 1 | 07/02/2024 | Department 6 | 11/02/2024 | 6 | -6 |
Here's the general approach for these patterns. Needs some refinement as some of the rules are open to interpretation. Your week numbers were off too.