Forum Discussion

buttercream's avatar
buttercream
Icon for Helper II rankHelper 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 ...
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" )
     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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.