Forum Discussion

Glennboy's avatar
Glennboy
New Member
2 years 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 d...
Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    2 years 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

muhammad_786_1's avatar
muhammad_786_1
Icon for Solution Supplier rankSolution Supplier
2 years 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