Customer Inactivity Matrix

[mpatel974]

Hello,

I'm trying to achieve the following thing in a matrix : 

A for active, I for inactive

M-1  M0  M+1  M+2  M+3  M+4  …

A         I      A                                      = A group

A         I       I        A                            = B group

A         I       I         I       A                   = C group

A         I       I         I        I        A         = D group

Etc

This is what i did : 

Status client = if([CA HT NET]>0.00,"A","I")

The most difficult part is to count the number of same pattern A I I I I A etc.. and assign it to a group.

Can you please help me on this or show me a way to achieve it ?

Kind regards,

Mohammad

[v-xiaotang]

Hi @mpatel974 

Has the problem been solved? If so, you may accept the appropriate post as the solution or post your own solution to help other members find it quickly. Thanks.

If not, could you kindly add more details? Because not very clear about the number you want to count, it would be nice if you can mark the number count and its formula on the sample data.

Besides, could you share the sample file after removing sensitive information?

Best Regards,

Community Support Team _ Tang

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

[mpatel974]

Hello @v-xiaotang,

As I said in my post, I want to count the pattern AIIII...A.

M    M+1  M+2  M+3  M+4    …

A         I      A                                      = A group

A         I       I        A                            = B group

A         I       I         I       A                   = C group

A         I       I         I        I        A         = D group

Etc

The rules is to filter on active customer (based on sales, the client is identified by idcli) on the 1st month and to study how long they stay inactive, to put them in a group and to count the number of occurences.

For example for "A" group I have 15 customers that follows this pattern.

Here the link for sample data :

https://drive.google.com/file/d/1TRw0_U9ML1LE4h8zTcUveLhlKQrA_l3T/view?usp=sharing

Kind regards,

[v-xiaotang]

Hi @mpatel974 

To make it easier to see the test results, I remove part of the data from your file as my sample file because the data in your file is too large.

Here are steps and you can take it for reference.

-

Steps:

Create the measures,

M_0 = MINX(ALLSELECTED('Calendar'),'Calendar'[Date])

SC_0 = CALCULATE( [Status client], FILTER('Calendar','Calendar'[Calendar Month Year] =FORMAT(_Measure[M_0],"mmm yyyy")) )

SC_1 =
CALCULATE (
    [Status client],
    FILTER (
        'Calendar',
        'Calendar'[Calendar Month Year]
            = FORMAT ( EDATE ( _Measure[M_0], 1 ), "mmm yyyy" )
    )
)

SC_2 =
CALCULATE (
    [Status client],
    FILTER (
        'Calendar',
        'Calendar'[Calendar Month Year]
            = FORMAT ( EDATE ( _Measure[M_0], 2 ), "mmm yyyy" )
    )
)

Group_A = 
COUNTROWS(FILTER(Customers,[SC_0]="A"&&[SC_1]="I"&&[SC_2]="A"))

Result:

If you need to count Group_B, then add the measures:

SC_3 =
CALCULATE (
    [Status client],
    FILTER (
        'Calendar',
        'Calendar'[Calendar Month Year]
            = FORMAT ( EDATE ( _Measure[M_0], 3 ), "mmm yyyy" )
    )
)

Group_B =
COUNTROWS(FILTER(Customers,[SC_0]="A"&&[SC_1]="I"&&[SC_2]="I"&&[SC_3]="A"))

, and so on..

See sample file attached bellow.        

Best Regards,

Community Support Team _ Tang

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

[MFelix]

Hi @mpatel974 ,

Without any specif information about your data model is difficult to give you an answer, can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[mpatel974]

Hello @MFelix,

Thanks for your answer :

Here the data model, very simple one :

I have sent to you the link for the pbix file.

Kind regards,

Mohammad