How to DISTINCTCOUNT all IDs from the earliest Date until the date specified in another column?

[D_PBI]

Hi,
This request for help involves two tables (snippets of both are below):
dimDate

CustomerPurchases

The relationship between the two tables is dimDate[Date] 1-* CustomerPurchases[Purchase Date].

I'm expecting the output to show in the following fashion. Ignore the red error indications - I've renamed the DAX so to hide sensitive column names.
The slicer has the Financial Year and Financial Quarter (as an expanable node) from the dimDate table.
The table visual has the Financial Year and Financial Quarter from the dimDate table. The 'Customer IDs' contains the measure I'm trying to create. The DAX present is my latest effort (although I've tried various efforts).

I would like the measure to count all DISTINCT CustomerPurchases[Customer ID] records from the earliest CustomerPurchases[Purchase Date] up until the dimDate[Max Financial Quarter Date].
For example, looking at the dimDate table, for Financial Year 2023/24 and Financial Quarter 2, the dimDate[Max Financial Quarter Date] is the 2024/01/31. Therefore I would like the 'Customer IDs' measure to DISTINCTCOUNT all Customer IDs from the earliest CustomerPurchases[Purchase Date] to the (and including) 2024/01/31.
This same logic should apply to each Financial Year and Financial Quarter, regardless if the slicer is used or not. It's the row context that should be ascertaining the dimDate[Max Financial Quarter Date] to use as the max date in the measure.

I hope this makes sense.
How do I acheive this?
Thanks.

[D_PBI]

Bump - is anyone able to help, please?

[amitchandak]

@D_PBI , You can use inplace of DISTINCT CustomerPurchases[Customer ID])

lastnonblankvalue(DimDate[Date], DISTINCTCOUNT(CustomerPurchases[Customer ID]))

or refer

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[D_PBI]

@amitchandak thank you for your response. Unfortunately, it didn't work. I've spent another few hours searching the net attemtping to achieve my aim but without success.

The table I'm attempting to perform the cumulative DISTINCTCOUNT on is shown in the screenshot below - it's just a snippet of the entire table.

The end result should be in the form of the below screenshot.

The slicer visual contains the Year and Quarter from the dimDate table.
The table visual contains the Year and Quarter from the dimDate table and the following columns:
'cumulative measure' - this is the measure I need help in creating.
_minDisclosureDate - this is the earliest Disclosure Date in the Disclosures table above.
Min Financial Quarter Date - this is the start date for the row's Disclosures[Disclosure Date]'s Year and Quarter.
Max Financial Quarter Date - this is the end date for the rows Disclosures[Disclosure Date]'s Year and Quarter.

The relationship is dimDate[Date] 1-* Disclosures[Disclosure Date].

My latest DAX for the 'Cumulative attempt' measure is in the screenshot below (I've used 'Disclosures' in place of the actual table name).

Cumulative attempt =

VAR _earliestDisclosureDate = CALCULATE( MIN( Disclosures[_minDisclosureDate] ), ALL( Disclosures ) )

VAR _maxFinancialQuarterDate = MAX( Disclosures[Max Financial Quarter Date] )

VAR _result =

    CALCULATE(

        DISTINCTCOUNT( Disclosures[contactid] ),

        FILTER(

            ALL( Disclosures ),

            Disclosures[Disclosure Date] >= _earliestDisclosureDate

            && Disclosures[Disclosure Date] <= _maxFinancialQuarterDate

        )

    )

RETURN

    _result


What the 'Cumulative attempt' measure is suppose to calculating is to DISTINCTCOUNT Disclosures[contactid] where the Disclosures[Disclosure Date] is between the Disclosures[_minDisclosureDate] and the Disclosures[Max Financial Quarter Date].
You can see the Disclosures[_minDisclosureDate] is always the same (01/01/1970), but the Disclosures[Max Financial Quarter Date] will differ depending on the dimDate[Financial Year] and dimDate[Financial Quarter] value of the row's context.
So the 'Cumulative attempt' for Year 2023/24 Quarter 2 should be a DISTINCTCOUNT of Disclosures[contactid] from the earliest Disclosures[Disclosure Date] until the end date in the rows's Disclosures[Max Financial Quarter Date].
This would be the same calculation for each Year and Quarter combination. It should still perform even when the user uses the slicer visual to only showing certains years/quarters in the table visual.

The numbe being returned are present is 5889 where, when testing through SQL and our CRM, the correct number is 2488. That's a massive difference and I don't believe it's anything due to the DISTINCTCOUNT not counting unique contactids - althought I could be wrong - I don't know what's wrong.

Please help someone as this is holding me up??
Thanks.