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
ouma
Frequent Visitor

DAX that looks up value from another table2 and return data where date in table2 <= date in table1

Hello Guy

This problem seems simple but been stuck with it for a bit. I have two table call it Table1 and Table2. I want to create a calculated column in Table1 that picks the exchange rate from table2 where;

1. The dates in Table1 and Table2 Match

2. If there is not date match in the two tables the return the closest earlier date match. I have attached the two tables and the results I would like to achieve with the new calculated column

Looking forward to your assistance, thanks

 

ouma_0-1684328532243.png

What is would like to achieve in the new calculated column

ouma_1-1684328598576.png

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@ouma , Try like

 

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] <=_date), Table1[Exchange Rate])

View solution in original post

Thanks Amitchandak for your quick response. Your DAX helped me a lot, I improved it as below and it worked

 

VAR _date =

        TOPN (

            1,

            FILTER (

               Table2, table2[Date] <= table1[Date]

            ),

            table2[Date],

            DESC

        )

    RETURN

        MAXX (

                _maxDate,

                Table1[Exchange Rate]

        )

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@ouma , Try like

 

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] <=_date), Table1[Exchange Rate])

Thanks Amitchandak for your quick response. Your DAX helped me a lot, I improved it as below and it worked

 

VAR _date =

        TOPN (

            1,

            FILTER (

               Table2, table2[Date] <= table1[Date]

            ),

            table2[Date],

            DESC

        )

    RETURN

        MAXX (

                _maxDate,

                Table1[Exchange Rate]

        )

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.