Forum Discussion
Cumulative Distinct Count within Time Series / Running totals
v-piga-msft I edited my post to reflect my desired output.
Hi Forum,
I'm struggeling to find a solution for the following problem: I want to count the first occurrence of "User ID" and also display its cumulative sum over the time.
I achieve to get the first occurrence by using the following column:
First Occurrence = CALCULATE(FIRSTNONBLANK(Report[Transcript Completed Date];1);ALLEXCEPT(Report;Report[User ID]))
I'm struggeling to get the cumulative distinct counts.
A solution would be to create a new table with only the unique values, which already worked. However, I want to use the same table though, in order to use the same slicer in the report.
My data looks like this:
Transcript Completed Date | User ID |
07.13.2017 08:18 AM | name@client_1.com |
10.21.2016 12:15 PM | name@client_1.com |
02.19.2018 08:45 AM | name@client_1.com |
05.11.2016 12:00 AM | name@client_1.com |
08.14.2018 02:30 PM | name@client_2.com |
08.29.2018 06:48 AM | name@client_2.com |
11.30.2017 02:28 PM | name@client_2.com |
02.02.2018 11:36 AM | name@client_2.com |
03.22.2018 01:14 PM | name@client_2.com |
06.25.2017 05:39 PM | name@client_2.com |
03.29.2018 04:41 PM | name@client_3.com |
04.16.2018 02:23 PM | name@client_3.com |
04.13.2018 02:35 PM | name@client_3.com |
05.19.2018 12:28 PM | name@client_3.com |
08.04.2018 04:32 PM | name@client_4.com |
09.01.2018 09:26 PM | name@client_4.com |
03.06.2018 10:20 PM | name@client_4.com |
08.30.2017 12:58 PM | name@client_4.com |
Desired output for a visual.
| Cumulative Unique User | Uniqe User |
2016 | 1 | 1 |
2017 | 3 | 2 |
2018 | 4 | 1 |
I was looking through the forum already, but am not able to find the correct solution.
Thanks in advance :)
Hi Anonymous,
Based on your First Occurrence column, you could create another two measures with the formula below to achieve your desired output.
Unique user = CALCULATE ( COUNT ( Report[Transcript Completed Date] ), ALLEXCEPT ( Report, 'Report'[User ID] ), FILTER ( 'Report', 'Report'[Transcript Completed Date] = 'Report'[First Occurrence] ) ) Cumulative Unique User = CALCULATE ( [Unique user], FILTER ( ALL ( 'Report' ), 'Report'[Transcript Completed Date] <= MAX ( 'Report'[Transcript Completed Date] ) ) )Here is your desired output.
Best Regards,
Cherry
1 Reply
v-piga-msftResident Rockstar
Hi Anonymous,
Based on your First Occurrence column, you could create another two measures with the formula below to achieve your desired output.
Unique user = CALCULATE ( COUNT ( Report[Transcript Completed Date] ), ALLEXCEPT ( Report, 'Report'[User ID] ), FILTER ( 'Report', 'Report'[Transcript Completed Date] = 'Report'[First Occurrence] ) ) Cumulative Unique User = CALCULATE ( [Unique user], FILTER ( ALL ( 'Report' ), 'Report'[Transcript Completed Date] <= MAX ( 'Report'[Transcript Completed Date] ) ) )Here is your desired output.
Best Regards,
Cherry