Forum Discussion
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 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))
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.
5 Replies
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-Forum/ba-p/963216
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/1447523lbendlin 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 Thank you. PLease also indicate YearWeek numbers and add sample data where issues were opened in December 2023.
