Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Currency conversion

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    4 years ago

    Hello Anonymous,

     

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

     

    Cheers!

    Vivek   

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.