Tracking User Changes Over Time

[Anonymous]

Suppose I have a list of users like so:

Usernames     Date  
A             7/1/18   
B             7/1/18 
A             8/1/18   
C             8/1/18  
D             8/1/18
A             9/1/18
C             9/1/18
D             9/1/18
E             9/1/18

And I am interested at comparing the number of users from one month to another, and finding which users was added or removed.

For example:

1) user B was removed, and user C and D were added in August compared to July.

2) user E was added, and no users were removed in September

I want a column that would return something like this:

Usernames     Date      WhatHappened
A             7/1/18    
B             7/1/18    Removed in August, 2018
A             8/1/18    Nothing
C             8/1/18    Added in August, 2018
D             8/1/18    Added in August, 2018
A             9/1/18    Nothing
C             9/1/18    Nothing
D             9/1/18    Nothing
E             9/1/18    Added in September, 2018

 Any help would be greatly appreciated!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use following link if it suitable for your requirement:

Comment =
VAR currDate = 'Table'[Date]
VAR currList =
    CALCULATETABLE (
        VALUES ( 'Table'[Usernames] ),
        FILTER (
            ALL ( 'Table' ),
            FORMAT ( [Date], "yyyymm" ) = FORMAT ( currDate, "yyyymm" )
        )
    )
VAR prevList =
    CALCULATETABLE (
        VALUES ( 'Table'[Usernames] ),
        FILTER (
            ALL ( 'Table' ),
            [Date] < DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
        )
    )
VAR nextList =
    CALCULATETABLE (
        VALUES ( 'Table'[Usernames] ),
        FILTER (
            ALL ( 'Table' ),
            FORMAT ( [Date], "yyyymm" )
                = FORMAT ( DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, 1 ), "yyyymm" )
        )
    )
RETURN
    IF (
        COUNTROWS ( prevList ) > 0
            && NOT [Usernames] IN prevList,
        "New Added in " & FORMAT ( [Date], "mmm yyyy" ),
        IF (
            COUNTROWS ( nextList ) > 0
                && NOT [Usernames] IN nextList,
            "Removed in " & FORMAT ( [Date], "mmm yyyy" ),
            "Nothing"
        )
    )

Regards,

Xiaoxin Sheng