Currency conversion

[Anonymous]

Can anyone be help?

Hi everyone.

I have a table with transaction like this:

 and another table with rate of currency per day like this:
 

My client want to have a two slicer:
- Currency 
-Periodo of time

They want to select a time period and a currency and have the transaction amount. Power BI needs for the selected date range, read the transaction date and read the rate table and make the conversion. This is very dynamic and I don't see how to solve it.

Can anyone be help?

[vivran22]

Hello @Anonymous,

Will your currency rate table have multiple records for each currency for a given date?

Cheers!

Vivek   

[Anonymous]

Yes.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You may refer to my sample.

Transaction:

Rate - EUR:

I create a Currency table by dax to build slicer.

Currency = VALUES('Rate - EUR'[Currency])

Measures:

Basic Measure = 
VAR _Rate = IF(MAX('Transaction'[currency]) = "EUR",1,CALCULATE(SUM('Rate - EUR'[Rate - EUR]),FILTER(ALL('Rate - EUR'),AND('Rate - EUR'[Date]=MAX('Transaction'[Date]),'Rate - EUR'[Currency]=MAX('Transaction'[currency])))))
VAR _EUR = DIVIDE(SUM('Transaction'[Value]),_Rate)
VAR _SELECTCURRENCY = SELECTEDVALUE('Currency'[Currency])
VAR _SELECTRATE = CALCULATE(SUM('Rate - EUR'[Rate - EUR]),FILTER(ALL('Rate - EUR'),AND('Rate - EUR'[Currency] = _SELECTCURRENCY,'Rate - EUR'[Date] = MAX('Transaction'[Date]))))
VAR _SELECTVALUE = _EUR*_SELECTRATE
RETURN
IF(ISFILTERED('Currency'[Currency]),_SELECTVALUE,_EUR)

Value in Select Currency = SUMX('Transaction',[Basic Measure])

What Currency now? = IF(ISFILTERED('Currency'),SELECTEDVALUE('Currency'[Currency]),"EUR")

Build a date range slicer by Date column from Transaction table. Result is as below.

If there is no selection in currency slicer, [Value in Select Currency] measure will show values in rate of EUR. [What Currency now?] will show EUR.

If there is a selection in currency slicer like USD, [Value in Select Currency] measure will show values in rate of USD. [What Currency now?] will show USD.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.