Inactive users (no data in the last 60 days)

[mussaenda]

Hello, 

I have a simple request received but I can't make it work. So I need to show daily who are the inactive users. Inactive users mean they don't have any activity in the last 60 days regardless of the category.

I have 3 tables: date table, activity_log, users

users

user	creation_date
aa	01/01/2024
bb	01/01/2024
cc	03/01/2025
dd	06/01/2025
ee	10/01/2025
ff	15/01/2025
gg	29/01/2025

activity_log

user	activity_date	category	amount
aa	03/01/2024	open_trades	100
aa	15/01/2024	closed_trades	200
bb	12/04/2024	transaction	100
cc	07/07/2025	transaction	100
dd	14/05/2025	open_trades	100
dd	29/05/2025	closed_trades	50
dd	14/06/2025	open_trades	100
ee	20/06/2025	open_trades	50
ff	02/05/2025	transaction	100

and of course  the date table..

The chalIenge I am encountering is to show daily (via graph and table) who are those. I can't do the relationship between the users and the date.

The result I am achieving is something like this (according to the provided data)
Selected Filter: year: 2025, month: July
result:

daliy date (from date table)	inactive users (from users)
08/07/2025	4
07/07/2025	4
06/07/2025	5
05/07/2025	5
04/07/2025	5
03/07/2025	5
02/07/2025	5
01/07/2025	4

4 = (gg, ff, bb, aa)

5 = (gg, ff, cc, bb, aa)

depends on the filter of year and month, inactive users will dynamically change according to the date that we have in the activity_log.

I know this is doable, I am lost how to calculate the inactive users.

[wardy912 · Accepted solution]

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!

[danextian · Accepted solution]

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.

[mussaenda]

Hi @danextian , 

just one question, the -5 in days diff (from the screenshot) should also be flagged as inactive user, right? because it is not within the 60day period

[v-lgarikapat]

Hi @mussaenda ,

Thanks for reaching out to the Microsoft fabric community forum.

@danextian ,

@wardy912 ,

Thanks for your prompt response

@mussaenda Thanks for the  follow up question

But actually, no, the Day Diff = -5 should not be flagged as an inactive user

Based on logic

Users are inactive if their latest activity date is more than 60 days before the selected date (in this case, July 1, 2025).

Details:

User cc has a Latest Activity Date of 2025-07-07.

Your slicer-selected date is 2025-07-01.

So the Day Diff is 2025-07-01 - 2025-07-07 = -6 ( report shows -5 might be adifferent calculation).

This means cc was active after July 1 in other words, they are active, not inactive.

If you are still encountering any challenges, we would be happy to assist you further.

Best Regards,

Lakshmi Narayana

[mussaenda]

Hi @v-lgarikapat ,

if the day diff is negative the scenario will be same as gg user, since it will look like the user did not have any activity in the selected date range. And in this case, it will result as inactive.

[v-lgarikapat]

Hi @mussaenda ,

Thank you for the follow-up question.

As per the logic, if the last activity date is within the past 60 days, it would fall under the Active case

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @mussaenda ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out we’d be happy to continue supporting you.

We truly appreciate your continued engagement and thank you for being an active and valued member of the community. If you’re still experiencing any challenges, we’re more than happy to assist you further.

We look forward to hearing from you.

Best regards,
Lakshmi

[v-lgarikapat]

Hi @mussaenda ,

I just wanted to check if your issue has been resolved. If you still have any questions or need help, feel free to reach out  I’m happy to assist.

Thank you for being an active part of the community. Looking forward to hearing from you!

Best regards,
Lakshmi

[wardy912 · Accepted solution]

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!

[mussaenda]

Hi @wardy912 ,

I tried the suggestion, it is working in the small dataset but when I applied it to my dataset (having around 90M rows), it cannot be handled. 

[wardy912]

Hi @mussaenda 

 Unfortunately I can't replicate your issue as I don't have a dataset large enough, so I've asked Copilot to suggest a fix. This is AI Generated and unvalidated. I hope it helps but I can't guarantee it will.

Copilot
When working with a large dataset (like 90 million rows) in Power BI or DAX, performance issues often arise due to:

🔍 What's Likely Going Wrong
Row-by-row filtering:
The use of FILTER(activity_log, activity_log[user] = users[user]) is row context dependent and not optimized for large datasets. This creates a row-by-row comparison, which is very slow at scale.

Inefficient relationships:
Even though you're using a date table, the indirect relationship between users and activity_log via DAX can be expensive.

Repeated calculations:
The LastActivityDate is recalculated inside IsInactiveUser, which is then recalculated for every row in InactiveUsersCount.

✅ Optimized Approach
Here’s how you can optimize this logic:

1. Precompute Last Activity Date per User
Instead of calculating it dynamically, create a calculated column or a summary table:

UserLastActivity = 
SUMMARIZE(
    activity_log,
    activity_log[user],
    "LastActivityDate", MAX(activity_log[activity_date])
)

This creates a smaller table with one row per user and their last activity date.

2. Join This Table to Users
Create a relationship:

users[user] → UserLastActivity[user] (One-to-One)
Or use LOOKUPVALUE in a calculated column:

users[LastActivityDate] = 
LOOKUPVALUE(
    UserLastActivity[LastActivityDate],
    UserLastActivity[user], users[user]
)

3. Create a Calculated Column for Inactivity
This avoids recalculating in measures:

users[IsInactive] = 
VAR LastActivity = users[LastActivityDate]
VAR CurrentDate = TODAY()  -- or use MAX('date'[date]) in a measure
RETURN
    IF(
        ISBLANK(LastActivity) || DATEDIFF(LastActivity, CurrentDate, DAY) > 60,
        1,
        0
    )

4. Measure for Inactive Users Over Time
Now this becomes much faster:

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

📈 Performance Tips
Use summary tables instead of row-by-row filters.
Avoid FILTER with table[column] = value inside measures for large datasets.
Use calculated columns or Power Query to precompute values.
Consider aggregating activity_log in Power Query before loading into the model.

[mussaenda]

Thanks @wardy912 ,

I wrapped the user field in the users table with VALUES to make it work in measures. 
Will let you know of the results. 

Thanks again, @wardy912