Forum Discussion
Cumulative Distinct Count by Date
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!
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
4 Replies
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
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?Anonymous,
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,
SturlaEDIT: 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'