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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
New_hello188
Helper I
Helper I

To return value based on start and end date column with condition.

Hello All , 

 

I need to refer to table B and get the appropriate exchange rate for a transaction based on the posting date (table A).  Ignore the currency code as report need to be presented in SGD value. 

 

condition 1 : If the posting date falls between the starting date and ending date specified in Table B, then the corresponding exchange rate in Table A should be used for that transaction.

 

condition 2 : If there is no ending date specified in Table B and the posting date is after the starting date, then the exchange rate to be applied should be the last row of exchange rates listed in Table B.

 

New_hello188_0-1686839258998.png

 

Is there any method to meet this condition ? Thanks ! 

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@New_hello188 , A new column in table 2

 


Maxx(filter(Table2, Table1[Currentcy Code] = table2[currency code] && Table1[Date] >= Table2[Start Date] &&( isblank(Table2[End Date]) || Table1[Date] <= Table2[End Date]) ) , Table2[Exchnage_Rate])

 

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

 

Thank you ! 

Could you please guide , how can I also apply filter at the currency code at Table B

The actual data in Table B consists of lots of different currency code and I only need SGD rate return to Table A even though the currency code is in USD. 

 

I tried to modify your measure to meet above condition , but it was not working. 

 

Maxx(filter(Table2, Table1[Currentcy Code] = table2[currency code] = "SGD" && Table1[Date] >= Table2[Start Date] &&( isblank(Table2[End Date]) || Table1[Date] <= Table2[End Date]) ) , Table2[Exchnage_Rate])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors