Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculating all cases open for each month

Hi I have case data that includes open and close date (as well as cases where there is no close date yet) and I want to be able to show how many cases were open each month - that could be cases that have opened in the month, but also needs to include cases that opened in prior months AND cases that closed in that month. 

 

The data I have looks like this:

 Case IDOpen DateClose Date
 a1/1/202311/15/2023
 b1/19/2024 
 c12/20/2022 
 x10/15/202312/2/2023
 y7/4/2022 
 z1/2/20241/20/2024

 

This is what I want to be able to show:

MonthCases Open
November 20233
December 20233
January 20244
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Icon for Super User rankSuper User
    2 years ago

    Hello Anonymous ,

     

    how did you get the result in the expected outcome based on the data you've provided ?

     

    like where did the 3, 3 ,4 come from.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi! Thanks for the question. I got to those numbers because during those months that's how many active cases were open. So if "case y" opened in July 2022, but has not closed it should be counted in the Cases Opened column in November 2023/December 2023/January 2024 because it is still open in all of those months. I don't need a count of what had an open date in those months. So I guess the better way to phrase it would be a count of active cases month to month.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Anonymous ,

     

    Not quite sure how you came up with Cases Open in your expected results, but based on your description, there are more than just three months that hold results, so you can check out my methodology.

     

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
var _table=
CALENDAR(
    DATE(2022,1,1),
    DATE(2024,2,1))
return
ADDCOLUMNS(
    _table,
    "Month_Year",FORMAT([Date],"mmmm")&" "&YEAR([Date]))

    2. Create calculated column.

    Test =
var _test=
FILTER(
    'Table 2',
    IF('Table'[Close Date]<>BLANK(),
    'Table 2'[Date]>=EARLIER('Table'[Open Date])&&'Table 2'[Date]<=EARLIER('Table'[Close Date]),
    'Table 2'[Date]>=EARLIER('Table'[Open Date])&&'Table 2'[Date]<=EOMONTH(EARLIER('Table'[Open Date]),0)))
var _table=
SUMMARIZE(
    _test,[Month_Year])
return
CONCATENATEX(
    _table,[Month_Year],"-")

    3. Create measure.

    Cases Open =
COUNTX(
    FILTER(ALL('Table'),
    CONTAINSSTRING(
       'Table'[Test] ,MAX('Table 2'[Month_Year]))=TRUE()),[Case ID])

    4. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Lui! I got to those numbers because during those months that's how many active cases were open. So if "case y" opened in July 2022, but has not closed it should be counted in the Cases Opened column in November 2023/December 2023/January 2024 because it is still open in all of those months. I don't need a count of what had an open date in those months. So I guess the better way to phrase it would be a count of active cases month to month. Does this process work for that?