Convert Currency Conversion with Slicer

[na12063]

Hi, let's say I have two tables. OrderDetail_Table, has 5 days of data transactions in USD, EUR and GBP and Currency_Table 5 days currency exchange data for USD, EUR and GBP.
I want to be able to change(currency) of the total_amount based on the currency slicer and to match the date of the order 

How would you create a connection in the Date Column  as well the currency conversion and get the converted column as Total_Amount(Selected Currency).

Similar to this Video but to match the date.

https://www.youtube.com/watch?v=88QSf2fB1Rg&t=2s

Here below I created Sample Data in PBIX

https://1drv.ms/u/s!ArgoBkRUwmVklwPKYO6q4t_rEpKs

Thanks in advance,

na12063

[v-cherch-msft · Accepted solution]

Hi @na12063 

You may try to use 'Unpivot columns' in query editor for Currency table.Link the two tables with date column.Then create CurrencyByLocation columns and 

SelectedCurrency,Total_Amount(Selected Currency) measures.For further,please refer to the attachment.

SelectedCurrency =
CALCULATE (
    SUM ( 'Currency'[Value] ),
    FILTER (
        'Currency',
        'Currency'[Attribute] = SELECTEDVALUE ( OrderDetail[CurrencyByLocation] )
    )
)

Total_Amount(Selected Currency) =
DIVIDE ( SUM ( OrderDetail[Total_Amount] ), [SelectedCurrency] )

Regards,

Cherie

[na12063]

Dear Cherie,

I appriciate your help on this solution it's working as expected. Is there any way we can add currency symbol to the values to the column Total_Amount(Selected Currency) or I have to create another column with only symbols based on CurrencyByLocation column. 

Please let me know your comments.

Best Regards,

na12063