How to Calculate Retention on a monthly base

[Asmaa-elsheikh]

Hello dears, I want to calculate the retention rate on a monthly basis & need to know how to calculate the number of users who has orders in Aug & has orders in Sep I have searched on the community & tried some of the suggested solutions, but It does not work with my data, here is

dummy data Appreciate your support

[Selva-Salimi]

Hi @Asmaa-elsheikh 

you can follow these steps:

1. create a column which shows and order on your date column based on month ( better to write this column column which is in this format "YYYYMM" assume that we call this column year_month) :

   monthly_order = calculate (count(year_month) , filter (your_table , year_month <= earlier (year_month))

2. use this monthly_order column in your measure as follows:

measure retention_rate := 

 var current_users = summarize(your_table , ID)

 var previous_users= summarize (filter(all(your_table) , monthly_order =selectedvalue(monthly_order)-1) , ID)

 return

 countrows(intersect(current_users , previous_users))

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[Aasif]

• Change the date to month using power query. Then use group by option.

[Asmaa-elsheikh]

Thanks @Aasif, but this solution will return the count of active users each month not the retained users 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Asmaa-elsheikh]

Thanks @lbendlin, here is the data as table format

Orders	ID	date
40	1	7/31/2024
1	1	8/26/2024
4	1	2024-08-31
33	2	7/31/2024
31	2	8/26/2024
4	2	2024-08-31
1	3	7/31/2024
32	3	8/26/2024
35	3	2024-08-31
1	4	7/31/2024
4	4	8/26/2024
1	4	2024-08-31
40	1	7/31/2024
1	1	8/26/2024
4	1	2024-08-31
33	2	7/31/2024
31	2	8/26/2024
4	2	2024-08-31
1	3	7/31/2024
32	3	8/26/2024
35	3	2024-08-31
1	4	7/31/2024
4	4	8/26/2024
1	4	2024-08-31
40	1	7/31/2024
1	1	8/26/2024
4	1	2024-08-31
33	2	7/31/2024
31	2	8/26/2024
4	2	2024-08-31
1	3	7/31/2024

[lbendlin]

2024-09-31, really ?

Retention is 100% for all IDs.  Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.