Forum Discussion

buttercream's avatar
buttercream
Helper II
1 year ago
Solved

Flag consecutive values based on multiple columns

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.

 

CustomerDatePurchase IDConsecutive
A4/30/20251125No
B5/20/20251244No
C3/5/20251003Yes
C3/5/20251004Yes
C3/5/20251006Yes
D6/10/20251356No
D6/11/20251357No
E6/12/20251391No
E6/12/20251395No
Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    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" )
     

     

3 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    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" )
     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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.