Forum Discussion
Helper ISearch for a value in a two dimension table
Hello,
Being a new power BI user i need some help,
i have a bill list on a table formated as following :
Date |Currency|amount
01/10/2020|GBP |20.05
05/60/2019|CHF |18.64
10/05/2018|EUR |10.36
08/07/2017|USD |34.26
i would like to add calculated collumn converting all prices in euro with the date exact exchange rate, for this i have an other table with all currecy change rates compared to euro for all dates as following :
(DD/MM/YYYY)
Date |GBP |USD |CHF
01/01/2017|1.055 |0.950 |1.081
02/01/2018|1.048 |0.955 |1.086
03/01/2018|1.051 |0.958 |1.084
. | . | . | .
. | . | . | .
. | . | . | .
11/10/2020| 1.067 |0.970 |10.096
12/10/2020| 1.070 |0.975 |10.090
but i can't figure out how to get the change rate for the two parameters (concerned currency and bill date) to do the calcultation, is there a way to do that in power BI?
Thank you for your answers !
@wahib_mouhoubi , Despivot the second table. then you can join in the currency and get the rates
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/You can qualify in the first table as (Table1)
Currency rate: maxx(FILTER(Table2,Table2[Currency]-Table1[Currency] && Table2[Date]-Table1[Date]),Table2[Rate])
3 Replies
- amitchandak
Super User
@wahib_mouhoubi , Despivot the second table. then you can join in the currency and get the rates
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/You can qualify in the first table as (Table1)
Currency rate: maxx(FILTER(Table2,Table2[Currency]-Table1[Currency] && Table2[Date]-Table1[Date]),Table2[Rate])
wahib_mouhoubiThat's it, thank you!
Hi wahib_mouhoubi,
I agree with amitchandak 's point of view, unpivot column share help to expand all the records.
After these steps, you can refer to the following blog to build relationships through multiple fields.
Relationship in Power BI with Multiple Columns
Regards,
Xiaoxin Sheng
