Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Update a query table

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...
Need Help
  • CahabaData's avatar
    CahabaData
    10 years ago

    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's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Anonymous
Not applicable
10 years ago

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,