Inactive users (no data in the last 60 days)

Hi mussaenda 

You should have a dedicated date table and have these relationships:

activity_log[activity_date] → date[date] (Many-to-One)
users[creation_date] → date[date] (Many-to-One)

We don't need a direct relationship between users and date, we can use DAX to bridge them as follows:

 First, we need to create a measure for the last activity date for each user

LastActivityDate = 
CALCULATE(
    MAX(activity_log[activity_date]),
    FILTER(
        activity_log,
        activity_log[user] = users[user]
    )
)

Next, create a measure to calculate inactivity

IsInactiveUser = 
VAR CurrentDate = MAX('date'[date])
VAR LastActivity = 
    CALCULATE(
        MAX(activity_log[activity_date]),
        FILTER(
            activity_log,
            activity_log[user] = users[user] &&
            activity_log[activity_date] <= CurrentDate
        )
    )
RETURN
    IF(
        DATEDIFF(LastActivity, CurrentDate, DAY) > 60 || ISBLANK(LastActivity),
        1,
        0
    )

Finally, count inactive users each day

InactiveUsersCount = 
CALCULATE(
    COUNTROWS(users),
    FILTER(
        users,
        [IsInactiveUser] = 1 &&
        users[creation_date] <= MAX('date'[date])
    )
)

To visualise the data, use a line chart or a table, add the date to the axis and add the measure 'inactiveusercount' to the value.

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

Hi mussaenda 

Create a measure that calculates the latest activity date per uyser which reverts back to the creation date if there's no activity log

Latest Activity Date = 
COALESCE (
    CALCULATE (
        LASTNONBLANK ( activity[activity_date], COUNTROWS ( activity ) ),
        ALLEXCEPT ( users,users[user ] )
    ),
    MIN ( users[creation_date] )
)

Create another measure that evaluates over the list of users and checks the  date difference betweeen their latest activity date aginst a specific date

Inactive Users = 
VAR _days = 60
VAR _tbl =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            users[user ],
            "@latest activity date", [Latest Activity Date]
        ),
        "@days since last active", DATEDIFF ( [@latest activity date], MAX ( Dates[Date] ), DAY )
    )
VAR _inactive =
    FILTER ( _tbl, [@days since last active] >= _days )
RETURN
    COUNTROWS ( _inactive )

However, I don't get how you came up with 3 for 1 July. aa, bb, ff, gg are past 60 days on this date

Please see the attached pbix.