DAX: lookupvalue takes first previous date value

[Kai_Mai]

Hi!

Need help!

If someone can solve it I would be very greatful!

I have 2 tables.

Table one:

Has customer_id, session, session_date, session_group.

Table two:

Has customer_id_, invoice_sum, invoice_date.

I need DAX that looks up value in table two when session_group = "4" and takes invoice_sum value to table one.

Also I need conditions that customer_id in table 1 = customer_id in table 2.

Tricky part is that session_date and invoice_date dont match always and mostly session date is after invoice date.

So I need also a condition that looks up first invoice_sum that is before session_date (starting form session_date).

Thank you in advance!

[johnt75 · Accepted solution]

I think the below will work as a calculated column on Table 1

invoice_sum =
IF (
    'Table 1'[session_group] = 4,
    VAR currentCustomer = 'Table 1'[customer_id]
    VAR currentSession = 'Table 1'[session_date]
    VAR invoiceSum =
        MAXX (
            CALCULATETABLE (
                TOPN ( 1, 'Table 2', 'Table 2'[invoice_date] ),
                'Table 2'[customer_id] = currentCustomer
                    && 'Table 2'[invoice_date] <= currentSession
            ),
            'Table 2'[invoice_sum]
        )
    RETURN
        invoiceSum
)

[Kai_Mai]

Thank you so so much!