Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to Solution.
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 )
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
75 | |
68 | |
41 | |
35 |
User | Count |
---|---|
107 | |
56 | |
52 | |
48 | |
40 |