Consecutives 3 months for unique IDs

[Anonymous]

Hi experts,

I have a question about writing dax formula to find the loyal users who can meet 3 requirement as follows:

1. online bills > 50%,

2. online sales > 50%

3. consecutives 3 months

IDs	Month (in another dim table)	Online Bills vs Total	Online Sales vs Total	Consecutive 3 months	Loyal user
A	01/2022	40%	30%
A	02/2022	50%	70%
A	03/2022	60%	30%	x	x
B	01/2022	20%	40%
B	02/2022	30%	50%
B	04/2022	70%	30%
B	05/2022	10%	70%
B	06/2022	20%	70%	x	x

My question is: 

1. Is it possible to create a sepearate table as above?

2. There is one more table I tried to add, I want to check how long does it take from first order month to the 3rd consecutive month

IDs	First Order Month
A	1/2022
B	2/2022
C	1/2022

Would appreciate any ideas. Thanks a lot in advance.

[v-zhangti]

Hi, @Anonymous 

You can try the following methods to tell if it is 3 consecutive months.

Column:

Next order date =
MINX (
    FILTER (
        'Dim table',
        [Month] > EARLIER ( 'Dim table'[Month] )
            && [IDs] = EARLIER ( 'Dim table'[IDs] )
    ),
    [Month]
)

is consecutive? = 
Var _interval=DATEDIFF([Month],[Next order date],MONTH)
Return
IF(_interval=BLANK(),BLANK(),IF(_interval<>1,1,BLANK()))

Count = 
Var _count=CALCULATE(COUNT('Dim table'[Month]),FILTER('Dim table',[IDs]=EARLIER('Dim table'[IDs])))
Var _ids=CALCULATE(MAX('Dim table'[IDs]),FILTER('Dim table',[is consecutive?]=1))
Var _date1=CALCULATE(MAX('Dim table'[Month]),FILTER('Dim table',[is consecutive?]=1))
Return
IF([IDs]=_ids&&[Month]<=_date1,BLANK(),_count)

Consecutive 3 months =
CALCULATE (
    COUNT ( 'Dim table'[Month] ),
    FILTER (
        'Dim table',
        [Count] <> BLANK ()
            && [IDs] = EARLIER ( 'Dim table'[IDs] )
            && [Month] <= EARLIER ( 'Dim table'[Month] )
    )
)

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.

[Anonymous]

Hi @v-zhangti , thanks a lot for your support, would you mind explaining the dax that you suggested?

[v-zhangti]

Hi, @Anonymous 

[Next order date] Finds the next order date for each ID in the table. With the DATEDIFF function, you can determine the direct interval between the order date and the next order date. The normal should be 1 month, if not the return is not 1, then consider it not consecutive months.

[is consecutive?] If it is equal to 1, it means that the date less than the corresponding date of this row is not consecutive and needs to start recounting on the next row equal to 1. [Count] is the output of consecutive rows. [Consecutive 3 months] counts consecutive rows over time.

Hope that helps.

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.

[Anonymous]

Hi Charlotte,

I got your point! However, when I check the data, even though the store did not buy consecutive but it still counts 7 as the picture below

Could you please help me how to fix it?

[v-zhangti · Accepted solution]

Hi, @Anonymous 

Please check the attachment.

In this case, 2 consecutive counts are counted for 3 consecutive months. I'm sorry I haven't thought of a better way for the time being, but I can only help you in any way I can.

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.

[daXtreme]

Sorry but the logic is not clear and the table does not make it any clearer. Please make it more understandable.