Rank recurring payment dates per order id (consecutive dates)

[Glennboy]

I have recurring payments, some of which succeed and some fail. Those which fail are attempted again the next day and then some of those will succeed and some will fail (up to 5 consecutive payment dates)

Data looks something like this:

Order id	Pay Date	Success/Fail
123	29/07/2024	Fail
123	30/07/2024	Fail
123	31/07/2024	Fail
123	01/08/2024	Fail
123	02/08/2024	Success
124	04/07/2024	Fail
124	05/07/2024	Success
124	04/08/2024	Success
125	03/07/2024	Success
125	03/08/2024	Success
126	05/07/2024	Success

If there is not a payment date for the same order on the previous day then it should be ranked 1, consecutive payments to be ranked accordingly

What I want is this:

Order id	Pay Date	Success/Fail	Pay Sequence
123	29/07/2024	Fail	1
123	30/07/2024	Fail	2
123	31/07/2024	Fail	3
123	01/08/2024	Fail	4
123	02/08/2024	Success	5
124	04/07/2024	Fail	1
124	05/07/2024	Success	2
124	04/08/2024	Success	1
125	03/07/2024	Success	1
125	03/08/2024	Success	1
126	05/07/2024	Success	1

I feel like this shouldn't be too difficult but I just cannot get it right! 

Thanks

[glennwatts2002]

Thanks so much for your response.

This is almost what I need, but I'm afraid that I failed to relay another scenario! 

These are recurring payments, so it's quite possible that they had multiple successful payments and then a failure.

When I apply the DAX code above I see the following: 

As you can see, the counter is cumulative and although the first payment attempt in June shown above has correctly reverted to 1, the next one is showing 5 (cumulative count), so I think there needs to be an extra clause to ensure that each sequence starts from scratch and increases by one for each attempt

[muhammad_786_1 · Accepted solution]

Hi @Glennboy 

You can use this DAX formula to rank payments. It resets the rank to 1 if there is no payment date for the same order on the previous day, and increments the rank for consecutive payments accordingly:

Pay Sequence =

VAR CurrentOrder = 'Payments'[Order id]

VAR CurrentDate = 'Payments'[Pay Date]

VAR PreviousDate =

    CALCULATE(

        MAX('Payments'[Pay Date]),

        FILTER(

            'Payments',

            'Payments'[Order id] = CurrentOrder &&

            'Payments'[Pay Date] < CurrentDate

        )

    )

RETURN

    IF(

        DATEDIFF(PreviousDate, CurrentDate, DAY) = 1,

        CALCULATE(

            COUNTROWS('Payments'),

            FILTER(

                'Payments',

                'Payments'[Order id] = CurrentOrder &&

                'Payments'[Pay Date] <= CurrentDate

            )

        ),

        1

    )

Please see, if this is what you want.

Best Regards,

Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn