Flag consecutive values based on multiple columns

[buttercream]

Hi all,

Need your help.  I'm trying to flag customers that made multiple purchases in the same day.  The purchase ID must be within 3 numbers of each other.  So the same customer on the same day makes purchase ID 100, 103, and 104 should all be flagged as consecutive.  Below is a sample table and the consecitive column is what I'm trying to calculate.

Customer	Date	Purchase ID	Consecutive
A	4/30/2025	1125	No
B	5/20/2025	1244	No
C	3/5/2025	1003	Yes
C	3/5/2025	1004	Yes
C	3/5/2025	1006	Yes
D	6/10/2025	1356	No
D	6/11/2025	1357	No
E	6/12/2025	1391	No
E	6/12/2025	1395	No

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])))=1,"No",if(or(Data[Purchase ID]-CALCULATE(MAX(Data[Purchase ID]),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])&&Data[Date]=EARLIER(Data[Date])&&Data[Purchase ID]<EARLIER(Data[Purchase ID])))<=3,and(CALCULATE(min(Data[Purchase ID]),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])&&Data[Date]=EARLIER(Data[Date])&&Data[Purchase ID]>EARLIER(Data[Purchase ID])))-Data[Purchase ID]<=3,CALCULATE(min(Data[Purchase ID]),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])&&Data[Date]=EARLIER(Data[Date])&&Data[Purchase ID]>EARLIER(Data[Purchase ID])))-Data[Purchase ID]>0)),"Yes","No"))

Hope this helps.

[MasonMA · Accepted solution]

Hi @buttercream 

Consider building a new Column in your table with below:

VAR ThisCustomer = Purchases[Customer]

VAR ThisDate = Purchases[Date]

VAR ThisID = Purchases[Purchase ID]

VAR OtherPurchases =

    FILTER (

        Purchases,

        Purchases[Customer] = ThisCustomer &&

        Purchases[Date] = ThisDate &&

        ABS ( Purchases[Purchase ID] - ThisID ) <= 3 &&

        Purchases[Purchase ID] <> ThisID

    )

RETURN

IF ( COUNTROWS ( OtherPurchases ) > 0, "Yes", "No" )

[buttercream]

Perfect.  Thank you !!!