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.

     

     

     

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.