Calculate number of cases prior to earliest date in a slicer

[Bentley]

I need a way to capture all the open cases before the first date in a slicer to capture a case backog.  For instance, if the slicer is set between 10/1/2020 and 12/31/2020, I would like to see all the cases that are still open that were created before 10/1/2020.  

This is as close as I've gotten but I don't think I'm headed in the right direction.  This should count all open cases and the subtract all the ones within the current date range.  The problem with this is that it also captures those cases created after the end date of the slicer (12/31/2020 in the example above).

Date Driven Backlog = calculate(count('Case'[CaseNumber]),removefilters(RT_Dates[Date]),'Case'[IsClosed]=False)-CALCULATE(count('Case'[CaseNumber]),'Case'[IsClosed]=FALSE())

I can't share the actual file but I tried to recreate the issue in this Sample  file.   On the "Problem" tab  I have a prodcut slicer and a date sclicer and a single card.  The result I'm trying to achieve in the card is 5.  This should be all the orders submitted before 4/15/1998 that have not yet shipped.  The expected results are on the "Expected Tab".

Thanks in advance for your help.

[Anonymous]

My solution works. If not say what is missing

[Bentley]

It works perfectly.  I accepted it as the solution.  If you need me to do something else so you recevie credit for solving it I'm happy to do so.  Please let me know if there is something else you need me to do.

[Anonymous · Accepted solution]

In your example you say you expect a result of 5 

However if you count the order ids of the products before 15-4 you only get 2 order ids

Here is what i did to help you along:

I took the Min of your date filter. MIN (Date[Date])

I want to get all open orders before that date

DateDrivenBacklog2 =

VAR DateMin = MIN(DimDate[Date])

VAR x = calculate(count('Orders'[OrderID]),removefilters(DimDate[Date]),Orders[OrderDate]<DateMin,ISBLANK(Orders[ShippedDate]))

RETURN x

I took all orders before the minimum Date and included a filter for the open orders

If you replace the count by

calculate(count('Products'[ProductID])...........

Then you get the count of the products (5)

[Bentley]

@Anonymous  - Thank you.   This appears to be exactly what I needed.  Thank you for your help.

[amitchandak]

@Bentley , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Bentley]

@amitchandak - Thank you for the reply.  Would the sample file in the original post work or would you need to see something else. While the data is not the same, it does replicate the problem I'm trying to solve for.  Thank you.