Calculating all cases open for each month

[jharris32]

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 ID	Open Date	Close Date
	a	1/1/2023	11/15/2023
	b	1/19/2024
	c	12/20/2022
	x	10/15/2023	12/2/2023
	y	7/4/2022
	z	1/2/2024	1/20/2024

This is what I want to be able to show:

Month	Cases Open
November 2023	3
December 2023	3
January 2024	4

[v-yangliu-msft]

Hi  @jharris32 ,

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

[jharris32]

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?

[Idrissshatila]

Hello @jharris32 ,

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.

[jharris32]

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.