Cumulative Distinct Count by Date

Hi Anonymous,

here is one way to achive what you are looking for:

I assume that you already have a date table in your modell.

In your data table, you need to add a endDate column:

EndDate =
VAR endDate =
    CALCULATE (
        MIN ( [Date] );
        FILTER (
            Table;
            Table[StartDate] > EARLIER ( Table[StartDate] )
                && Table[CustomerID] = EARLIER ( Table[CustomerID] )
        )
    )
RETURN
    IF ( ISBLANK ( endDate ); DATE ( 9999; 12; 31 ); endDate )

Then add this as a measure:

numberOfCustomerAboveScore =
VAR limit = 40
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table[CustomerID] );
        FILTER (
            ALL ( 'Table' );
            MAX ( 'Date'[Date] ) >= Table[Date]
                && MAX ( 'Date'[Date] ) <= Table[EndDate]
                && Table[Score Running Total] >= limit
        )
    )

You can use the limit value to decide your treshold.

best regards,

Sturla