Update a query table

[Anonymous]

Hello,

I have a beginners question. I have two tables. Table1 consists of transactions including a date-of-delivery field and an amount. In the second table I have a date field, a currency field and a conversion rate.

I want to update all amounts in table 1 by multiplying the amount with the conversion rate for the right date... Seems easy enough, but the currency table does not consist of all dates that occur in the transaction table.

Example:

The currency table have conversion rates for the dates:

2016-01-01

2016-01-04
2016-01-05
...

And in the transaction table, I have a lot of transactions that have occured on the date 2016-01-02...

So what I want to do is to try and match the transaction date with the currency date. If there is a match I want to update the amount field by multiplying the amount by the conversion rate. If the transaction date cannot be found, I want to update the amount field with the latest conversion rate. In the example above the transactions for 2016-01-02 would be updated with the conversion rate of the date 2016-01-01

Any help would be appreciated.

[Anonymous]

Hi CHewbacca,

KGrice’s point seems well, you can also follow below steps:

1. Create tables.

Currency table.

 

Delivery table.

2. Add calculate columns to get the last valid date and rate values from currency table.

Dax:

LastDate =

var temp= [currency]

var currentDate=[Deliver Date]

return

if(MAXX(FILTER('Currency', 'Currency'[Date]<=currentDate && 'Currency'[currency]=temp),'Currency'[Date])<>BLANK(),MAXX(FILTER('Currency', 'Currency'[Date]<=currentDate && 'Currency'[currency]=temp),'Currency'[Date]),currentDate)

Rate = if( LOOKUPVALUE('Currency'[conversion rates],'Currency'[currency],[currency],'Currency'[Date],[LastDate])<>BLANK(),LOOKUPVALUE('Currency'[conversion rates],'Currency'[currency],[currency],'Currency'[Date],[LastDate]),1)

Result:

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for your interest and answers. I am struggling with this. Even though I got KGrice’s suggestion to work, I need to do some more changes to the table. So your solution seems to do what I am looking for but I am not exactely sure about what it does. 

My tables are 'Transactions' and 'ExchangeRate'. I do not need to create them since they already exist.

TransactionsExchangeRateSo I am looking to add a column in the 'Transactions' table with corresponding exchange rates, so that I can convert the column 'Oms SEK' from Euro to SEK.

Can you please explain more in detail what I need to do? 

Thanks,

[KGrice]

Hi @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])

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

This worked very well!

Thank you!