Forum Discussion

Glennboy's avatar
Glennboy
New Member
1 year ago
Solved

Rank recurring payment dates per order id (consecutive dates)

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 idPay DateSuccess/Fail
12329/07/2024Fail
12330/07/2024Fail
12331/07/2024Fail
12301/08/2024Fail
12302/08/2024Success
12404/07/2024Fail
12405/07/2024Success
12404/08/2024Success
12503/07/2024Success
12503/08/2024Success
12605/07/2024Success

 

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 idPay DateSuccess/FailPay Sequence
12329/07/2024Fail1
12330/07/2024Fail2
12331/07/2024Fail3
12301/08/2024Fail4
12302/08/2024Success5
12404/07/2024Fail1
12405/07/2024Success2
12404/08/2024Success1
12503/07/2024Success1
12503/08/2024Success1
12605/07/2024Success

1

 

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

Thanks

Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    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

2 Replies

  • muhammad_786_1's avatar
    muhammad_786_1
    Solution Supplier
    1 year ago

    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

  • glennwatts2002's avatar
    glennwatts2002
    New Member
    1 year ago

    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