Help Beginner Cumulative Measure Over Time

[liz_lemon100]

Hey guys! I am new here. I need help and I can't figure it out. I will provide as much detail as possible.

Basically what I want to do is show my access rate over time. I have two measures
1. n_accessed calculates the distinct number of users who have accessed our platform. If a user accessed the platform their status is "accessed".
2. access_rate calculates how many accessed the platform out of our total user base (100)

Right now my data shows that if 12 access our platform in March the access rate is 12% and if 20 access in April then the access rate is 20%. Works great. 

I have a date filter on this report using a large dim_calendar table. This is connected to my fct_user table which contains the access status and their status date. Every user has a date for each status. So we know what date a user accessed or didn't access the platform. 

I want to create a line chart that shows how the access rate has changed over time. So from March to April a total of 32 users accessed the platform. That's 32%. But right now my line chart shows 20% in March and 12% in April. 

How do I calculate cumulative n_accessed over time and cumulative access rate? 


Please help! I am so lost and can't find help anywhere! Sorry if this is very elementary! 

[v-ssriganesh]

Hello @liz_lemon100,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @liz_lemon100,
Thank you for reaching out to the Microsoft fabric community forum.

I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[Shahid12523 · Accepted solution]

Create cumulative measures instead of monthly ones:

Cumulative distinct users:

n_accessed_cumulative =
CALCULATE (
DISTINCTCOUNT ( fct_user[UserID] ),
FILTER (
ALL ( 'dim_calendar'[Date] ),
'dim_calendar'[Date] <= MAX ( 'dim_calendar'[Date] )
),
fct_user[Status] = "accessed"
)

Cumulative rate:

access_rate_cumulative =
DIVIDE ( [n_accessed_cumulative], 100 ) // or [TotalUsers]

Use these in your line chart with dim_calendar[Month] → X-axis. This way March = 12%, April = 32% cumulative, not reset each month.

[liz_lemon100]

You are an angel! This worked perfectly! Thank you sooo much. Could you explain how the FILTER (ALL) works? I was trying variations of this but it never worked for me. I didn't get my filters right. Thanks again!