Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kai_Mai
Frequent Visitor

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!

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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
)

Thank you so so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors