Calculating First Time Processed

Hi

I have a list of credit card transaction debit attempts for sales and want to find the % total of transactions that failed on the first attempt. (Each sale can have up to three debit attempts on consecutive days if the previous day’s debit attempt failed.)

For example, below shows Sale ID 3340926 failed on the first attempt on July 1st but succeeded on the second attempt on July 2nd.

My measure needs to look at each distinct Sale ID and see if the transaction record with the lowest date for that unique sale ID failed or was processed.

I’ve tried all morning but am getting nowhere.

Sample data attached.

Thanks

Sample Data.pbix (535.4 KB)

Hello @KieftyKids

You may try this:

Count Failed First Time =
VAR _MinDate = MIN(Transactions[Paid])
VAR _Status = “Failed”
VAR _Filter =
FILTER(
Transactions,
Transactions[Paid] = _MinDate
&& Transactions[Status] = _Status
)
VAR _Count =
CALCULATE(
COUNTROWS(Transactions),
_Filter
)
RETURN
_Count

Cheers!
Vivek

Hi Vivek

Thanks for replying. However, it doesn’t appear to be giving the correct answer.

Does the formula search through every Sale ID and look for the minimum date and then see if Status = “Failed” for that minimum date of that Sale ID?

@KieftyKids,

See how this works for you:

First in Power Query, I created the following columns, MinDate and First Attempt:

Then the following measure:

Pct First Attempt Failed = 

VAR Tot1stAtt =
SUM( Transactions[First Attempt] )

VAR Fail1st =
CALCULATE(
    COUNTROWS( Transactions ),
    FILTER(
        Transactions,
        Transactions[First Attempt] = 1 &&
        Transactions[Status] = "Failed"
    )
)

VAR Result =
DIVIDE(
    Fail1st,
    Tot1stAtt,
    0
)

RETURN
Result

image

I hope this is helpful. Full solution file attached.

  • Brian

eDNA Forum - Pct 1st Failed Solution.pbix (534.9 KB)

1 Like

Hi @KieftyKids, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi Brian

Thank you very much. This solution worked perfectly and I was able to conduct analysis on the first attempt success or failure.

I’m now trying to isolate the second and third attempts etc so that I can analyse these - example below. (We can have up to five retry attempts.)

Is there a way to create additional custom columns similar to the ‘1st Attempt’ column? Or is there an easier way? A calculated column using SWITCH?

I’ve added a custom column that seems to be working. Will see if I can build some formulas around this column.

image

Hi @KieftyKids, just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum.