Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Payment Gateway Decline Analysis

I get a csv dump of Payment Gateway with thousands upon thousand of records weekly.  Here is a sample set of transactions Where James Smith has bought 5 different products / subscriptions. James has ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Create following measures:

    min date = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Status]="Declined"))
    max date = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Custom]=MAX('Table'[Custom])&&'Table'[Status]="Declined"))
    _check = IF(MAX('Table'[Date])='Table'[min date],1,0)
    _check2 = IF(MAX('Table'[Date])<='Table'[max date]&&MAX('Table'[Date])>='Table'[min date],1,0)
    Total Amount = CALCULATE(SUM('Table'[Custom]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi  Anonymous ,

 

Create following measures:

min date = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Status]="Declined"))
max date = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Custom]=MAX('Table'[Custom])&&'Table'[Status]="Declined"))
_check = IF(MAX('Table'[Date])='Table'[min date],1,0)
_check2 = IF(MAX('Table'[Date])<='Table'[max date]&&MAX('Table'[Date])>='Table'[min date],1,0)
Total Amount = CALCULATE(SUM('Table'[Custom]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])))

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Kelly,

 

This almost got me home. 

 

Now as the first table shows the first time the decline happened. How can i show (Grouping by Subscribtion ID)

 

  • When the transaction got approved (after getting decline)-from the data we see the decline for subscription 3972 happened on 5/1/2021 while the succussful approved transaction happened on 5/10/2021 (9 days to approval).  
  • How many attempts were made before getting approval. --We see 2 declines (5/1/2021 and 5/7/2021)

 

One thing to keep in mind is that each decline and approval are to be considered as a pair. So that if the following month the card declines again we can see this subscriber ID twice in the list, the first time for 5/1/2021 decline and then a 6/1/2021 decline (if applicable)