[NEED HELP] Calculate COUNT of Blank Row with Conditions and plot a Monthly Chart

[superhayan]

Hello all, I am stuck with a problem I need to plot a monthly chart of open compliance cases.

The example data is as below. The case that has no close date yet is regarded as still open. So basically I need to count the no. of case that has BLANK close date with create date smaller than the end of every month (that is to include all cases that already exist by the end of each month).  I have created a Calendar table that is linked to the close date. The chart will use this Calendar [date] as x-axis because on the same chart I also need to show no. of closed cases by month (   =CALCULATE(COUNT([Case Close Date]), [Current Status] = "Closed")     ). 

Compliance Case ID	Case Create Date	Case Close Date	Current Status
1	01/05/2022	01/08/2022	Closed
2	01/11/2022		Waiting for info
3	01/03/2023	01/03/2024	Closed
4	01/04/2023	01/04/2024	Closed
5	01/05/2023		In Progress
6	01/07/2023	01/01/2024	Closed
7	01/10/2023		In Progress
8	01/01/2024		In Progress
9	01/03/2024	01/05/2024	Closed
10	01/04/2024		In Progress
11	01/04/2024		Waiting for info

The final product willl be similar to this ( I know how to get the yellow line. Just stuck on how to get the blue line.) 

I tried the below measure but it gets incorrect result. Is it becuase the create date has no relationship to the calendar table so it cannot generate the correct result? If so, how can I achieve the above chart when also showing no. of close cases each month?

=CALCULATE(
COUNT('Table'[Complaince Case ID]),
ISBLANK('Table'[Case Close Date]),
'Table'[Case Created Date] <= EOMONTH(MAX('Calendar'[Date]),0))

Thank you!!!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Close Case: = 
COUNTROWS (
    FILTER (
        Case_Fact,
        Case_Fact[Case Close Date] >= MIN ( Calendar_Dim[Date] )
            && Case_Fact[Case Create Date] <= MAX ( Calendar_Dim[Date] )
    )
)

Open Case: = 
COUNTROWS (
    FILTER (
        Case_Fact,
        Case_Fact[Case Close Date] = BLANK()
            && Case_Fact[Case Create Date] <= MAX ( Calendar_Dim[Date] )
    )
)