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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
DannyMiletic
Frequent Visitor

Comparing dates from 2 tables to select a value for the exchange rate

Hi Power BI Community - can anyone help me with the measure that I need to write to accomplish this? I am really striking out on this and need help. I have 2 tables; Table A - exchange rate table with rates posted at the end of each month, and table B - invoice table with dates, amounts in local currency and currency type. I need a measure that will compare the invoice_date and currency_ID to the exchange rate table and bring back the correct rate. The exchange rates are posted at the end of the month so that the 7/31/2020 exchange rate is applied to invoices during the month of July.

 

DannyMiletic_1-1685482988328.png

 

 

 

1 ACCEPTED SOLUTION
MarkLaf
Memorable Member
Memorable Member

Assuming VALID_UNTIL and CURRENCYFROM_ID are a composite key for Table A, you could add a calculated column to both tables and relate them that way to then pull over corresponding exchange rate. Calculated column DAX would look like:

 

'Table A'[ExId] = 
VAR _dt = 'Table A'[VALID_UNTIL] 
VAR _exId = 'Table A'[CURRENCYFROM_ID]
RETURN
_exId  * 10^8 
+ YEAR( _dt  ) * 10^4 
+ MONTH( _dt ) * 10^2 
+ DAY( _dt )

'Table B'[ExId] = 
VAR _dt = EOMONTH( 'Table B'[invoice_date], 0 )
VAR _exId = 'Table B'[Currency_ID]
RETURN
_exId  * 10^8 
+ YEAR( _dt  ) * 10^4 
+ MONTH( _dt ) * 10^2 
+ DAY( _dt )

 

 

View solution in original post

3 REPLIES 3
MarkLaf
Memorable Member
Memorable Member

Assuming VALID_UNTIL and CURRENCYFROM_ID are a composite key for Table A, you could add a calculated column to both tables and relate them that way to then pull over corresponding exchange rate. Calculated column DAX would look like:

 

'Table A'[ExId] = 
VAR _dt = 'Table A'[VALID_UNTIL] 
VAR _exId = 'Table A'[CURRENCYFROM_ID]
RETURN
_exId  * 10^8 
+ YEAR( _dt  ) * 10^4 
+ MONTH( _dt ) * 10^2 
+ DAY( _dt )

'Table B'[ExId] = 
VAR _dt = EOMONTH( 'Table B'[invoice_date], 0 )
VAR _exId = 'Table B'[Currency_ID]
RETURN
_exId  * 10^8 
+ YEAR( _dt  ) * 10^4 
+ MONTH( _dt ) * 10^2 
+ DAY( _dt )

 

 

Thank you - this was very helpful!

Thanks - I will try it.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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