Count the number of rows that satisfies date filter condition

[cferv_77]

Hello,

I am trying to create a measure that counts the number of cases open given a date.

I tried typing up this measure. I have date table and is has an active relationship with order date and inactive with date delivered. The idea is the find out the count of non-closed cases any point in time. 

The measure below doesn’t work the way the way I want it because it doesn’t seem to capture ALL the cases that fall under the filter condition.

Headcount =

var selected = SELECTEDVALUE('Date'[Date])

return

CALCULATE(COUNTA('Cases'[ID]), USERELATIONSHIP('Date'[Date],'Cases'[Date Delivered]), AND('Cases'[Order Date] <= selected, OR(ISBLANK('Cases'[Date Delivered]), selected  <'Cases'[Date Delivered]))) + 0

ID	Order Date	Item	Date Delivered
1	6/3/2019	2	3/26/2020
2	12/7/2020	3	3/24/2022
3	3/19/2018	4	3/25/2020
4	8/24/2020	9	3/20/2021
5	1/25/2021	6
6	9/8/2020	1	3/19/2021
7	8/2/2019	3	3/5/2020

[johnt75 · Accepted solution]

Open case =
var maxDate = MAX( 'Date'[Date])
return CALCULATE( COUNTROWS(Cases), REMOVEFILTERS('Date'),
Cases[Order date] <= maxDate &&
(Cases[Delivered date] > maxDate || ISBLANK(Cases[Delivered date])
)

[cferv_77]

Hello @johnt75 

Thanks for the solution, but then number looks off when I used it. And I think it check/test it with a table visual because I guess of the RemoveFilters() function. Is there an alternate solution?

EDIT: Apologies, I double checked. It gave me the correct number. I'll accept your solution.