Customer First Purchase Date Time after Failed Transactions - within next 24 hours

[Anonymous]

Good Day! 

I'm looking to fix one of the issues, where I have to find the Customer First Purchase Date Time after Failed Transactions - within next 24 hours

Also, Please find the Table : SampleData as below :

For example : If CustomerID - 60001 has failed transaction on DateTime (1/1/2022 10:30AM) then had he purchased within 24 hours after failed tranaction as per orderstatus Column value- "Failed".

And, CustomerID-60001 has next purchase with in 24 hours.

Considerational Note : CustomerID 60008 has multiple failed tranaction.

Looking forward to have your best response. 

Thanks in Advance 😊

[lukiz84 · Accepted solution]

Hi, the easiest way is to add a calculated column, something like this:

FirstPurchasedAfterFailed := 
   VAR LastFailed = 
       CALCULATE(
           MAX(SampleData[DateTime]),
           FILTER(
              SampleData,
              SampleData[CustomerId] = EARLIER(SampleData[CustomerId]) &&
              SampleData[DateTime] < EARLIER(SampleData[DateTime]) && 
              SampleData[orderstatus] = "Failed"
           )
        )
    RETURN
       IF(
          NOT(ISBLANK(LastFailed)) &&
          SampleData[orderstatus] = "Authorized" &&
          LastFailed >= SampleData[DateTime] - 1,
          SampleData[DateTime]
       ) 
              

In your new Column you then always have the latest succesful DateTime after a failed transaction.

[lukiz84]

You are welcome, have a nice weekend 🙂

[lukiz84 · Accepted solution]

Hi, the easiest way is to add a calculated column, something like this:

FirstPurchasedAfterFailed := 
   VAR LastFailed = 
       CALCULATE(
           MAX(SampleData[DateTime]),
           FILTER(
              SampleData,
              SampleData[CustomerId] = EARLIER(SampleData[CustomerId]) &&
              SampleData[DateTime] < EARLIER(SampleData[DateTime]) && 
              SampleData[orderstatus] = "Failed"
           )
        )
    RETURN
       IF(
          NOT(ISBLANK(LastFailed)) &&
          SampleData[orderstatus] = "Authorized" &&
          LastFailed >= SampleData[DateTime] - 1,
          SampleData[DateTime]
       ) 
              

In your new Column you then always have the latest succesful DateTime after a failed transaction.

[Anonymous · Accepted solution]

Hey! Lukiz84,

Thanks for your wonderful Script, I really appreciate your response.

Thanks a lot!