Forum Discussion
Update a query table
My comments are late. So it seems to me that this is 3 step. 1st step is make a new calculated column that adds the conversion value itself as another new column to your transaction table. This is easily done by joining the 2 tables by the date field - and then using the calculated column feature.
This will result in nulls in this new field for rows that have a date that do not join.
The 2nd step then is to use the Fill Down feature which will apply the Conversion value just above to the rows below.
The 3rd step then is to do another new calculated column that is the math of the amount * conversion value = conversion amount
Memorable MemberHi Anonymous. In your transactions table, you can add a new column from the Data view:
CurrencyRelationship = CALCULATE(MAX('Currency'[Date]), FILTER('Currency', 'Currency'[Date]<=Transactions[Delivery Date]))
That column will show you the most recently available conversion date for each row. Use that column to create a relationship between the Transactions table and the Currency table (from the new column to the Currency date). With that in place, you can use RELATED to get the right conversion rate. For example:
ConvertedAmount = Transactions[Amount] * RELATED('Currency'[Rate])Hello KGrice and thank you for your help. I need to do the changes in the query editor of power bi, because I want to do some more formatting to the table afterwards. So I got your example to work, but unfortunately it will not solve my problem. Any suggestions for query editor?
- CahabaData
My comments are late. So it seems to me that this is 3 step. 1st step is make a new calculated column that adds the conversion value itself as another new column to your transaction table. This is easily done by joining the 2 tables by the date field - and then using the calculated column feature.
This will result in nulls in this new field for rows that have a date that do not join.
The 2nd step then is to use the Fill Down feature which will apply the Conversion value just above to the rows below.
The 3rd step then is to do another new calculated column that is the math of the amount * conversion value = conversion amount
This worked very well!
Thank you!