Calculating active cases by month

[jharris32]

Hi! I have a data source that provides open and close date for cases. I need to be able to create a visual that shows how many ACTIVE cases were open in a month (so not only cases that opened that month, but anything that doesn't have a close date yet, and excluding anything that closed in a prior month). 

I have a calendar table and then my data source looks something like this:

Case ID	Open Date	Close Date
a	12/5/2023	12/05/2023
b	10/27/2023
c	5/16/2023	01/17/2024
d	05/06/2021
x	01/16/2021	10/02/2023
y	09/29/2022	10/24/2023
z	07/26/2022	12/13/2023

The results should look something like this: 

October 2023	6
November 2023	4
December 2023	5

This is the forumula for the measure I tried and it gets me close but I'm still off by a few cases each month:

Open Cases =

VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])

VAR MaxCDate = IF(ISBLANK(SelectedYear)||SelectedYear=YEAR(TODAY()),EOMONTH(TODAY(),0),MAX('Calendar'[Date]))

VAR X = CALCULATE([Handled Cases],

FILTER(ALL('Calendar'),

'Calendar'[Date]<=MAX('Calendar'[Date])

&&

MAX('Calendar'[Date])<=MaxCDate

),

'GAL Docket_Power BI'[Open Date]<=MaxCDate&&'GAL Docket_Power BI'[Close Date]>MaxCDate||ISBLANK('GAL Docket_Power BI'[Close Date])=TRUE())

RETURN X

Any thoughts on where I'm messing up??

[ryan_mayu · Accepted solution]

@jharris32 

pls try this

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Open Date]<=max('Table 2'[Date])&&('Table'[Close Date]>=min('Table 2'[Date])||ISBLANK('Table'[Close Date]))))

pls see the attachment below