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