powerbicumulative-frequency

Active users on a given date in a Month in Power BI

I am working to get cumulative distinct count of uids on daily basis. My dataset consists dates and UserIDs active on that date. Example : Say there are 2 uids (235,2354) appeared on date 2022-01-01 and they also appeared on next day with new uid 125 (235,2354,125) on 2022-01-02 At this point i want store cumulative count to be 3 not 5 as (user id 235 and 2354 already appeared on past day ).

My Sample Data looks like as follows:

https://github.com/manish-tripathi/Datasets/blob/main/Sample%20Data.xlsx

enter image description here

and my output should look as follows:

enter image description here

Solution

  • Here's one way that seems to work, using your linked Excel sheet as the data source.

    Create a new table:

    Table 2 = DISTINCT('Table'[Date])

    Add the columns:

    MAU = CALCULATE(
    DISTINCTCOUNT('Table'[User ID]),
    'Table'[Date] <= EARLIER('Table 2'[Date]))

DAU = CALCULATE(DISTINCTCOUNT('Table'[User ID]),
    'Table'[Date] = EARLIER('Table 2'[Date]))

    Result from your Excel data
    enter image description here