Forum Discussion

cjemmott's avatar
cjemmott
Advocate IV
9 years ago

Cumulative Unique Count Slicing

Here is the plot I am trying to make:     It shows the cumulative unique count of user accounts (orange) and the count of users that took an action in each month (gray). It is limited to sho...
Need Help
Tutorial Requests
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi cjemmott,

 

Could you try the formula below to see if it works in your scenario? :smileyhappy:

User Accounts (monthly) =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        CALCULATE (
            COUNT ( 'users'[userId] ),
            FILTER (
                ALL ( 'users' ),
                'users'[createdAtMonth] <= MAX ( userActivity[Month] )
            )
        )
            - CALCULATE (
                DISTINCTCOUNT ( userActivity[User] ),
                FILTER (
                    ALL ( userActivity ),
                    userActivity[Month] = MAX ( userActivity[Month] )
                )
            )
            + DISTINCTCOUNT ( userActivity[User] ),
        BLANK ()
    )

 

Regards

cjemmott's avatar
cjemmott
Advocate IV
9 years ago

Unfortunately that gives the same result (blue line again).

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi cjemmott,

     

    Based on my tests, both the formulas below should work.

    User Accounts (monthly) =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        CALCULATE (
            COUNT ( 'users'[userId] ),
            FILTER (
                ALL ( 'users'[createdAtMonth] ),
                'users'[createdAtMonth] <= MAX ( userActivity[Month] )
            )
        ),
        BLANK ()
    )
    User Accounts (monthly) =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        CALCULATE (
            COUNT ( 'users'[userId] ),
            FILTER (
                ALL ( 'users' ),
                'users'[createdAtMonth] <= MAX ( userActivity[Month] )
            )
        )
            - CALCULATE (
                DISTINCTCOUNT ( userActivity[User] ),
                FILTER (
                    ALL ( userActivity ),
                    userActivity[Month] = MAX ( userActivity[Month] )
                )
            )
            + DISTINCTCOUNT ( userActivity[User] ),
        BLANK ()
    )

     

    Could you post your some sample/mock data which can be used to reproduce the issue, so that we can help further investigate on it? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

     

    Regards