Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 al...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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