Cumulative Distinct Count by Date

[undergreat]

Hello community, 

I am currently working on a internal Scoring System for our Customers. For every Action a customer performs, he get's a certain amount of points or some points are deducted. These points are accumulated and I have the raw data and calculated columns for totals and running totals by Date. The points are not calculated every day, but only when the user performs a specific action. 

But I am struggeling with a question that sounds rather simple but I cannot find the solution myself. So it would be great to get some help!

Here is what I need: I would love to see the (distinct) count of users above a certain threshold on any specific date. I would like to calculate a Measure or Column that counts how many users are above 100 on 01.01.2018, how many are abote 100 on 10.01.2018, how many are above 100 on 15.01.2018 and so on. 

Here is how my dataset looks like:

Date	CustomerID	Score	Rank	Score Running Total
01.01.18	99	10	1	10
02.01.18	99	5	2	15
03.01.18	99	3	3	18
04.01.18	99	-5	4	13
05.01.18	99	-5	5	8
06.01.18	99	20	6	28
01.01.18	105	5	1	5
02.01.18	105	6	2	11
03.01.18	105	10	3	21
04.01.18	105	-5	4	16
06.01.18	105	15	6	31

Would be great if someone could help me here!

 Thank you very much!

[sturlaws · Accepted solution]

Hi @undergreat,

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

[undergreat]

@sturlaws
Thanks so much for your help. Unfortunatel, I cannot make it work and I don't see what I did wrong. Could you please take a look?

[sturlaws]

@undergreat,

hm, I am not able to recreate that behaviour. Is there a relationship between the '03 - Score calculation' and the 'Calendar'-table?

Some other possible issues:

In the EndDate formula, I have made a typo CALCULATE( MIN([Date])... should be changed to CALCULATE( MIN('03 - Score calculation'[Date])..., but this should not be causing any issues.

In numberOfCustomersAboveScore the line

&& MAX ( 'Date'[Date] ) <= Sheet1[EndDate] should be changed to

&& MAX ( 'Date'[Date] ) < Sheet1[EndDate]

regards,
Sturla

EDIT: What data field did you put in the table? It should be the date from the Calendar-table, not the date from '03 - Score calculation'

[undergreat]

Thanks so much. It's a bit emberassing to tell: But the problem was the connection between the data and the calendar table. Works like a charm. Thank you!