DAX caclulated column to find the next date in column based on criteria or condition

[DoubtfulGuest]

Hi All,

I've been searching online to find a solution. It looks easy enough in SQL using partitioning, but beyond me in DAX. I have the table below and would like to add a calculated column for Payment date.

For each Customer , whenever there is a row where Transaction Type = Missed, return the Date when the next row appears for that customer with Transaction Type = Payment. If there is no Payment, leave blank. If it's any other type of Transaction Type, leave blank.

So for row 3 where there is a Missed, 20 and 31/03/2021....I'd like to add a new column to return 09/04/2021 as this when the next future Payment was made (row 6).

Once I get the Missed Date and Payment Date on one row - I'll be be able to do a DATEDIFF to calculate the delay in Payment.

Many thanks in advance for any guidance.

Customer	Transaction Type	Transaction Value	Date	Payment Date
0001	Direct Debit	20	31/01/2021
0001	Direct Debit	20	28/01/2021
0001	Missed	20	31/03/2021
0001	Fee	5	05/04/2021
0001	Misc	1	07/04/2021
0001	Payment	20	09/04/2021
0001	Direct Debit	20	31/04/2021
0001	Missed	20	31/05/2021
0001	Misc	7	02/06/2021
0001	Fee	8	06/06/2021
0001	Payment	20	12/06/2021
0001	Adjustment	9	14/06/2021
0002	Missed	100	31/03/2021
0002	Payment	100	15/04/2021
0002	Direct Debit	100	31/04/2021
0002	Missed	100	31/05/2021
0002	Adjustment	-29	03/06/2021
0002	Payment	100	27/06/2021
0003	Missed	39	15/03/2021
0003	Missed	39	15/04/2021

[CNENFRNL]

Date Deferred = 
IF(
    PYMT[Transaction Type] = "Missed",
    VAR date_deferred =
        MINX(
            FILTER(
                PYMT,
                PYMT[Customer] = EARLIER( PYMT[Customer] )
                    && PYMT[Date] > EARLIER( PYMT[Date] )
                    && PYMT[Transaction Value] = EARLIER( PYMT[Transaction Value] )
                    && PYMT[Transaction Type] = "Payment"
            ),
            PYMT[Date]
        )
    RETURN
        IF( date_deferred > 0, date_deferred - PYMT[Date] )
)

Excel worksheet formula is powerful to solve such a simple question,

[DoubtfulGuest]

@CNENFRNL- Thank you for your help. I really appreciate all the effort and depth you've gone to.

[mahoneypat · Accepted solution]

Although you should consider doing you calculation as a measure, here is a column expression that seems to work.

Payment Date =
VAR thisdate = Payments[Date]
RETURN
IF(
Payments[Transaction Type] = "Missed",
CALCULATE(
MIN( Payments[Date] ),
Payments[Transaction Type] = "Payment",
Payments[Date] >= thisdate,
ALLEXCEPT( Payments, Payments[Customer] )
)
)

Pat

[DoubtfulGuest]

@mahoneypat- Thank you so much for your help. Your solution suited me the most as it allows any first payment of any size to be allocated against the Missed...which is what I needed.

[davehus]

Hi @DoubtfulGuest , OK, so this is what I've come up with. There may be other ways but see below.

Here's the PBIX https://drive.google.com/file/d/1Pjg-lsaaeb_NpqUK7953vbuuqOAZQLis/view?usp=sharing

Start by creating a calculated table to filter to missed/payment - Test Table

Create ranking column to create an index

Offset calculation with give to the date to carry out your datediff.

Datediff for difference.

Hope this helps. Please accept my solution if it meets your needs.