Forum Discussion

Kai_Mai's avatar
Kai_Mai
Frequent Visitor
3 years ago
Solved

DAX: lookupvalue takes first previous date value

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!

 

dates
IF Functions
LOOKUPVALUE
  • johnt75's avatar
    johnt75
    3 years ago

    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
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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
)