DAX Formula to get field from a filtered row in the same table

Question

Using SSAS Tabular or Excel PowerPivot, I'm looking for a DAX formula to achieve the following:

I have a transactions table with 3 main columns: Date, Type and Amount

Type can be: 'Sale' or 'Payment'.

What I need is to get my new calculated field, the "Payment date" of each Sale, so my formula should look something like:

CALCULATE FIRST(Date), filter(type='payment'), filter(currentrow(date) <= payment(date)))

Need some help to find the correct date filter. It's also important that I find only the first transaction of type 'Payment', as there can be further transacations.

Example data: PaymentDate is my calculated field

Type        Date       Amount   PaymentDate

Sale     01/01/2016     100     31/02/2016
Sale     01/02/2016     100     31/02/2016
Sale     01/03/2016     100     31/05/2016
Payment  31/02/2016     200        Empty
Sale     01/04/2016     100     31/05/2016
Sale     01/05/2016     100     31/05/2016
Payment  31/05/2016     300        Empty
Sale     01/06/2016     100        Empty

Could you add a sample data and the expected result in your calculated column, in order to help you. — alejandro zuleta
I have more filters like Customer, but sure, I guess next date of type Payment will do the job — Jack Casas

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-09-20T13:33:26

Use the below expression in the calculated column:

=IF (
    'table'[Type] = "Payment",
    BLANK (),
    CALCULATE (
        MIN ( [Date] ),
        FILTER (
            'table',
            'table'[Date] > EARLIER ( 'table'[Date] )
                && 'table'[Type] = "Payment"
        )
    )
)

In your sample data there is an error, 31/02/2016 is not a valid date. I replaced it with 29/02/2016, the result is:

Let me know if this helps.

DAX Formula to get field from a filtered row in the same table

1 Answers