How to count unique values between the earliest date and a date in the column's row context?

[D_PBI]

Hi,
Please view the screenshot below. It is of the table that I am attempting to count the unqiue contactids between the earliest Entry Date and the row's context Max Financial Quarter Date. I did attempt to add the full dataset as a .CSV attachment but it's not supported.

In the above, I've sorted the Entry Date column by ascending order to show the earliest Entry Date present. You can see there are some blank entries. The earliest Entry Date is 01/01/1970 but the two entries that have this date do not have a contactid to include in the count.

The below screenshot is the same data table but sorted by Entry Date descending.

I need to create a measure (or column - whichever works) to count the unqiue contactids from the earliest Entry Date (which we know is 01/01/1970 - although that date has two blank contactid values) up until the date specfied by the row's Max Financial Quarter Date value.
The below example will hopefully explain more.

For the Year 2023-24 and Quarter 3, I need to count the unqiue contactids from the earliest Entry Date (which is 01/01/1970) until that Year and Quarter's Max Financial Quarter Date value (which is 30/04/2024).
The column named Test is my attempt at creating the measure - the formula is below:

Test =

CALCULATE(

    DISTINCTCOUNT( Table1[contactid] ),

    FILTER(

        Table1,

        Table1[Entry Date] <= EARLIER( Table1[Max Financial Quarter Date] )

    )

)

As you can see in the screenshot (above) the value returned is 5911. This is incorrect as when I test the same logic against the transactional system and through SSMS (using the same SQL Views as the PBI consumes) the value returned is 2482. 2482 is the correct value. The transactional system and SSMS values match, so it must be my PBI/DAX part that is wrong.

Once I'm correctly getting the value I'm expecting then I shall create the below table where there is only the Year and Quarter columns, and Test measure present. The Test measure needs to perform the same logic for each Max Financial Quarter Date end date associated to that Year and Quarter - like is shown in the below visual - but the Test values I know are wrong.

Please can some verify if what I'm doing is correct, or produce the necessary so I can achieve my aim?
Thanks.

[amitchandak]

@D_PBI , Create a date table join with date of your table and then create running total

examples

Cumm Sales = CALCULATE(DISTINCTCOUNT( Table1[contactid] ),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Based on Date = CALCULATE(DISTINCTCOUNT( Table1[contactid] ), Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[D_PBI]

@amitchandak - thank you for your prompt response. Something isn't quite right and I don't know what it is.
I do have a Date table named __dimDate. I have joined the __dimDate[Date] column to the Table1[Entry Date] column based on __dimDate[Date] >> 1-* >> Table1[Entry Date].

I've added your two measures using the names you specified. See below.

__Cumm Sales =

CALCULATE(

    DISTINCTCOUNT( Table1[contactid] ),

    FILTER(

        ALL( __dimDate ),

        __dimDate[Date] <= MAX( __dimDate[Date] )

    )

)

AND

__Cumm Based on Date =

CALCULATE(

    DISTINCTCOUNT( Table1[contactid] ),

    WINDOW( 1, ABS, 0, REL, ALL( __dimDate[Date] ), ORDERBY( __dimDate[Date], ASC ) )

)

The below screenshot shows what the results are for the two measures when I drag them into the same visual as I used in my opening post.

Both measures are producing the same result - that being 52. I should point out that the figure 52 figure if we are to count unique Table1[contactid] for the period Year 2023/24 and Quarter 3. If you remember the screenshot in my opening post, Quarter 3's date range is 01/02/2024 - 30/04/2024 (the same for each Year). So we when drag in the Year and Quarter fields, it's grouping  by Year 2023/24 and Quarter 3 resulting in the unique count of 52 - which is correct for the Quarter only.

I'm assuming one of the measures should be looking outside of the row context and counting all unique Table1[contactid] from the earliest Table1[Entry Date] (01/01/1970) up until to the date specified in the row's Max Financial Quarter Date value - which for Year 2023/24 and Quarter 3 will be between the dates 01/01/1970 - 30/04/2024 - which should be the unique Table1[contactid] count of 2482 for the full dataset I use.

To be clear, the Year used in the slicer is __dimDate[Date], and the fields used in the table visual are Table1[Year], Table[Quarter] and the other three columns are measures.

Please can you help me get this working?
Thanks.