Is this possible? A count driven by a date table but then to ignore it (my example will make sense)

[D_PBI]

Hi,
Please view the .PBIX file via the drop-box below. It contains the data and model then I need to perform my calculation on.
https://www.dropbox.com/scl/fi/vsbvn7rboy1v36avwf6b5/NoOfCasesClosed.pbix?rlkey=nw6gy96ez7hbsy9j6kbo...

You'll see I have a __dimDate_Case_DisclosureDate table filtering the Disclosures table.
The date slicer contains the custom Financial Year, and customer Financial Quarter from the __dimDate_Case_DisclosureDate table.
There is a matrix visual uses the custom Financial Year, and customer Financial Quarter from the __dimDate_Case_DisclosureDate table, and contains the 'NoOfCasesClosed' measure - it is this measure that I need help with.
I should add, in my non-dummy version of this .PBIX file, the matrix visual contains multiple measures that are driven by the __dimDate_Case_DisclosureDate.date value.

In additional to the .PBIX file, please see a screenshot of what I'm trying to get working.

I need the 'NoOfCasesClosed' measure to:
1) First include a list of Casesid where the Case's Disclosure Date is before the earliest row date context. So in the exmaple above the year 2020-21 is chosen which is the custom date period of 01/08/2020-31/07/2021. So the Cases to be included are before the 01/08/2020.
2) Next, the returned Cases are filtered to include only those that have a Case Status of 'Closed'.
3) Next, the returned Cases are then further filtered to include the Caseid where the Last Active Date value is between the date period selected. So in this example, to only include records with a Last Active Date between 01/08/2020-31/07/2021.

My DAX attempt is below. It returns 0 (zero) because the __dimDate_Case_DisclosureDate table is filtering the Disclosures table based on __dimDate_Case_DisclosureDate[Date] 1-* Disclosures[Disclosure Date], but then my DAX is then filtering it further on Disclosures[Disclosure Date] to be before the filtering date table. Hope this makes sense.
As I say, in my real .PBIX file the matrix visual contains other measures which are (and need to be) driven by the __dimDate_Case_DisclosureDate[Date] 1-* Disclosures[Disclosure Date] relationship.

I hope this makes sense.
Is there a way to achieve my aim using the model in the .PBIX file?
Thanks.

[D_PBI]

@gmsamborn - thank you. That works.

[gmsamborn · Accepted solution]

HI @D_PBI 

I added a line like this:

ALL( '__dimDate_Case_DisclosureDate' )

No Of Cases Closed = 
VAR _minDisclosureDate = MIN( __dimDate_Case_DisclosureDate[Date] )
VAR _maxDisclosureDate = MAX( __dimDate_Case_DisclosureDate[Date] )
VAR _result =
    CALCULATE(
        DISTINCTCOUNT( Disclosures[Caseid] ),
        ALL( '__dimDate_Case_DisclosureDate' ),
        Disclosures[Case Status] = "Closed",
        Disclosures[Disclosure Date] < _minDisclosureDate,
        AND(
            Disclosures[Last Active Date] >= _minDisclosureDate,
            Disclosures[Last Active Date] <= _maxDisclosureDate
        )
    )
RETURN
    _result

Let me know if you have any questions.

NoOfCasesClosed - mine.pbix