Forum Discussion

Ninja_Powered's avatar
Ninja_Powered
Frequent Visitor
2 years ago
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...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

     

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago

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

Ninja_Powered's avatar
Ninja_Powered
Frequent Visitor
2 years ago

lbendlin this is a simplied version of my data: 

 

IDDate OpenDate ClosedDepartment
107/02/2024 Department 6
224/01/202404/03/2024Department 6
324/01/2024 Department 6
424/01/202404/03/2024Department 6
524/01/202404/03/2024Department 6
624/01/202404/03/2024Department 6
724/01/2024 Department 6
819/01/2024 Department 2
919/01/2024 Department 2
1019/01/2024 Department 6
1119/01/202427/02/2024Department 7
1219/01/2024 Department 8
1308/01/202425/01/2024Department 1
1403/01/2024 Department 3
1503/01/202409/01/2024Department 4
1603/01/202417/01/2024Department 5
1703/01/202421/02/2024Department 5
1803/01/2024 Department 7
1903/01/202414/02/2024Department 8
2003/01/2024 Department 9
2103/01/202411/01/2024Department 10
  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • Ninja_Powered's avatar
      Ninja_Powered
      Frequent Visitor
      2 years ago

      lbendlin I have added some data from 2024 and some columns from the date table. 

       

      IDDate OpenDate ClosedDepartmentEnd of WeekWeek NumberCurrWeekOffset
      3203/12/2023 Department 903/12/202348-16
      3107/12/2023 Department 610/12/202349-15
      2719/12/202327/02/2024Department 724/12/202351-13
      2819/12/202328/02/2024Department 224/12/202351-13
      2919/12/202301/03/2024Department 224/12/202351-13
      3019/12/202301/03/2024Department 624/12/202351-13
      2124/12/202304/03/2024Department 624/12/202351-13
      2224/12/202304/03/2024Department 624/12/202351-13
      2324/12/202324/12/2023Department 624/12/202351-13
      2424/12/202324/12/2023Department 624/12/202351-13
      2524/12/2023 Department 624/12/202351-13
      2624/12/2023 Department 624/12/202351-13
      1403/01/202409/01/2024Department 407/01/20241-11
      1503/01/202411/01/2024Department 1007/01/20241-11
      1603/01/202417/01/2024Department 507/01/20241-11
      1703/01/202414/02/2024Department 807/01/20241-11
      1803/01/202421/02/2024Department 507/01/20241-11
      1903/01/2024 Department 307/01/20241-11
      2003/01/2024 Department 707/01/20241-11
      1308/01/202425/01/2024Department 114/01/20242-10
      819/01/202427/02/2024Department 721/01/20243-9
      919/01/2024 Department 221/01/20243-9
      1019/01/2024 Department 221/01/20243-9
      1119/01/2024 Department 621/01/20243-9
      1219/01/2024 Department 821/01/20243-9
      224/01/202404/03/2024Department 628/01/20244-8
      324/01/202404/03/2024Department 628/01/20244-8
      424/01/202404/03/2024Department 628/01/20244-8
      524/01/202404/03/2024Department 628/01/20244-8
      624/01/2024 Department 628/01/20244-8
      724/01/2024 Department 628/01/20244-8
      107/02/2024 Department 611/02/20246-6
      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        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.