Forum Discussion
Consecutives 3 months for unique IDs
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.
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.
6 Replies
daXtremeSolution Sage
Sorry but the logic is not clear and the table does not make it any clearer. Please make it more understandable.
v-zhangtiCommunity Support
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.
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.