Classify User Recurrence

[Syndicate_Admin]

Hello dear community.

Tengo la siguiente tabla en un csv, è incluso ya la tengo cargada en el Power BI.
+----------+----------+-----+
| fecha • msisdn • MAU •
+----------+----------+-----+
| 1-Jan-23 | 88880000 | 1 |
| 1-Jan-23 | 88880001 | 1 |
| 1-Jan-23 | 88880002 | 1 |
| 1-Jan-23 | 88880003 | 1 |
| 1-Feb-23 | 88880002 | 1 |
| 1-Feb-23 | 88880003 | 1 |
| 1-Feb-23 | 88880000 | 1 |
| 1-Mar-23 | 88880001 | 1 |
| 1-Mar-23 | 88880002 | 1 |
| 1-Mar-23 | 88880003 | 1 |
| 1-Mar-23 | 88880004 | 1 |
| 1-Mar-23 | 88880005 | 1 |
| 1-Apr-23 | 88880001 | 1 |
| 1-Apr-23 | 88880003 | 1 |
| 1-Apr-23 | 88880004 | 1 |
| 1-Apr-23 | 88880005 | 1 |
| 1-Apr-23 | 88880006 | 1 |
| 1-Apr-23 | 88880007 | 1 |
| 1-Apr-23 | 88880008 | 1 |
| 1-May-23 | 88880000 | 1 |
| 1-May-23 | 88880001 | 1 |
| 1-May-23 | 88880002 | 1 |
| 1-May-23 | 88880003 | 1 |
| 1-May-23 | 88880004 | 1 |
| 1-May-23 | 88880008 | 1 |
| 1-Jun-23 | 88880001 | 1 |
| 1-Jun-23 | 88880002 | 1 |
| 1-Jun-23 | 88880005 | 1 |
| 1-Jun-23 | 88880006 | 1 |
| 1-Jun-23 | 88880007 | 1 |
| 1-Jun-23 | 88880008 | 1 |
| 1-Jul-23 | 88880001 | 1 |
| 1-Jul-23 | 88880003 | 1 |
| 1-Jul-23 | 88880004 | 1 |
| 1-Jul-23 | 88880005 | 1 |
| 1-Jul-23 | 88880009 | 1 |
| 1-Jul-23 | 88880010 | 1 |
| 1-Jul-23 | 88880012 | 1 |
| 1-Aug-23 | 88880002 | 1 |
| 1-Aug-23 | 88880003 | 1 |
| 1-Aug-23 | 88880004 | 1 |
| 1-Aug-23 | 88880005 | 1 |
| 1-Aug-23 | 88880009 | 1 |
| 1-Aug-23 | 88880010 | 1 |
+----------+----------+-----+

This table contains information on numbers of unique phones (in the table this phone number is called [msisdn]) per month consisting of 01-Jan-2023 to 1-Aug-2023. In each month the phone number is unique, it is not repeated.

If you can see for example in the example table that I have provided, the phone number '88880001' appears 6 times from Jan-23 to Aug-23, 1 time in each month.

However, as a final result, I want to create a report in Power BI where the overall unique numbers appear with a new column called "[recurrence]". This new column has to be created in Power BI, either in DAX language or in M language. This column must be generated according to the following logics or conditions:

- if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" = 5, then put "recurring",
- if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" = 3 or 4, then put "casual",
- otherwise, if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" < 3, then put "new".

Based on the example table, this would be the result I hope to get:

+----------+------------+------------------+
| MSISDN | Cant times | Recurrence type |
+----------+------------+------------------+
| 88880000 | 1 | New |
| 88880001 | 5 | Recurring |
| 88880002 | 3 | Casual |
| 88880003 | 4 | Casual |
| 88880004 | 4 | Casual |
| 88880005 | 4 | Casual |
| 88880006 | 2 | New |
| 88880007 | 2 | New |
| 88880008 | 3 | Casual |
| 88880009 | 1 | New |
| 88880010 | 1 | New |
| 88880012 | 1 | New |
+----------+------------+------------------+

That is, although the data consists of 01-Jan to 01-Aug, I only need to make this evaluation based on 5 months from Mar-23 to Jul-23.

Here is the link where you can download the sample dataset and the expected result. I really appreciate you can help me with this solution:

[Syndicate_Admin]

Yeaaah!

This is exactly what I needed. No more, no less.

Thank you infinitely @Syndicate_Admin . 😎

[v-zhangti · Accepted solution]

Hi, @Syndicate_Admin 

You can try the following methods.

Column:

Count = CALCULATE(COUNT('Table'[msisdn]),
            FILTER('Table',[fecha]>=DATE(2023,3,1)
                            &&[fecha]<=DATE(2023,7,1)
                            &&[msisdn]=EARLIER('Table'[msisdn])))

Recurrence type = SWITCH(TRUE(),
[Count]=5,"Recurring",
[Count]=4||[Count]=3,"casual",
[Count]<3,"new")

Measure:

From = MINX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count])

To = Var _Max=MAXX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count])
Return
IF([From]=_Max,BLANK(),_Max)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.