Cumulative Distinct Count within Time Series / Running totals

[Anonymous]

@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 🙂

[v-piga-msft · Accepted solution]

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