DAX Measure for Open Issues Previous Week

[Ninja_Powered]

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 1000 issues were open (opened throughout the year not just week 51).

However, to make the visuals in my page work I need a seperate measure that automatically calculates the number of issues open in the previous week. I've tried to modify the formula below with no sucess. My date table has a week offset column I was trying to use... 

IssuesOpenInWeek = 
CALCULATE(
DISTINCTCOUNT('Issues'[ISSUE NUMBER]),
'Date Table'[End of Week] <= MAX('Date Table'[End of Week]),
REMOVEFILTERS('Date Table'[End of Week], 'Date Table'[Week Number]),
'Issues'[ISSUECLOSEDDATE] >= MAX('Date Table'[End of Week]) || 
ISBLANK('Issues'[ISSUECLOSEDDATE))

[lbendlin]

What is your definition of week?  Does your date table have a yearweek column?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Ninja_Powered]

@lbendlin this is a simplied version of my data: 

ID	Date Open	Date Closed	Department
1	07/02/2024		Department 6
2	24/01/2024	04/03/2024	Department 6
3	24/01/2024		Department 6
4	24/01/2024	04/03/2024	Department 6
5	24/01/2024	04/03/2024	Department 6
6	24/01/2024	04/03/2024	Department 6
7	24/01/2024		Department 6
8	19/01/2024		Department 2
9	19/01/2024		Department 2
10	19/01/2024		Department 6
11	19/01/2024	27/02/2024	Department 7
12	19/01/2024		Department 8
13	08/01/2024	25/01/2024	Department 1
14	03/01/2024		Department 3
15	03/01/2024	09/01/2024	Department 4
16	03/01/2024	17/01/2024	Department 5
17	03/01/2024	21/02/2024	Department 5
18	03/01/2024		Department 7
19	03/01/2024	14/02/2024	Department 8
20	03/01/2024		Department 9
21	03/01/2024	11/01/2024	Department 10

[lbendlin]

Thank you. PLease also indicate YearWeek numbers and add sample data where issues were opened in December 2023.

[Ninja_Powered]

@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

[lbendlin · Accepted solution]

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.