CODEWITHSUNDEEP
powerbidax
Mboolean
Mboolean

Reputation: 392

Sum of the total count (Running total based on User Count) - Is it possible?

I am trying to do the cumulative count for the users first time access the web page.

Table looks like,

UserID , Initial Access Date

    100, 2019-05-10 
    200, 2019-05-20 
    100, 2019-05-21 
    100, 2019-05-25 
    200, 2019-05-30 
    300, 2019-06-01

Current Expression:

Cumulative Total =
CALCULATE (
    DISTINCTCOUNT ( [USERID] ),
    FILTER (
        ALLSELECTED ( TABLE ),
        [INITIAL ACCESS DATE] <= MAX ( [INITIAL ACCESS DATE] )
    )
)

This only returns cumulative total count, How would I get the running total based on this count of Users.

Expected Results:

enter image description here

Upvotes: 1

Views: 48

Answers (2)

Mboolean
Mboolean

Reputation: 392

Finally I got it working without creating extra columns or measures,

Cumulative Total =

CALCULATE (
    SUMX (
        Table,
        IF ( DISTINCTCOUNT ( Table[UserID] ) > 0, 1, 0 )
    ),
    FILTER (
        ALLSELECTED ( Table ),
        Table[InitialAccessDate]
            <= MAX ( Table[InitialAccessDate] )
    )
)

Cheers!!

Upvotes: 0

JBfreefolks
JBfreefolks

Reputation: 163

1 - Upload your Fact table in Power Bi

2 - Create a Date Table 

Date = CALENDARAUTO()

Then

Year = Year('Date'[Date])
Month = MONTH('Date'[Date])
YEAR_MONTH = VALUE('Date'[Year])*100+VALUE('Date'[Month])

3 - Set relationships

4 - You can compute a set of two measures :

First 

InitialAccess = 
VAR InitialAccessInCurrentPeriod = 
DISTINCTCOUNT(Fact_T[UserID ])
RETURN
InitialAccessInCurrentPeriod

Then :

InitialAccessCumulated = 
VAR MaxDateInPeriod = MAX('Date'[Date])                      // Retrieve the last date in current filter context
VAR StartingDate = MINX(ALLSELECTED('Date');[Year])          // Retrieve the lowest year selected on slicer
RETURN
CALCULATE(
    [InitialAccess];                                        // Compute the number of initial access
    FILTER(                                                 // In a nex filter context where all the dates
        ALL('Date');                                        // Equal or superior to the lowest date selected
        'Date'[Year]>=StartingDate
        &&
        'Date'[Date]<= MaxDateInPeriod                       // Until the last date visible in the current row context
    )
)

You can see the final result here :

enter image description here

I have added to row to your fact table to have several years Here is the Fact table used :

UserID   Initial Access Date
50  12/12/2018
100 10/05/2019
200 20/05/2019
100 21/05/2019
100 25/05/2019
200 30/05/2019
300 01/06/2019
400 04/02/2020

Upvotes: 1

Related Questions