Forum Discussion

Andmi's avatar
Andmi
Frequent Visitor
7 years ago

Convert value from different currencies

Hi all,

 

I have an opportunity table with all the deals in different currencies where and in it a column called currencyisocode with the values of the currency of that line (EUR, USD, NZD....), I also have another table with the current exchange rates:

 

 

The problem is that I have end start dates for currencies and I need to use the one refering to that year. For example, this year I need to use the ones with startdate 01-01-2019.

 

How can I link the two measures and have all the values in the opportunity table converted when I do reports? How can I use the slicer then to change the currency?

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Andmi ,

     

    You can add condition to compare year part of startdate and current date and iso type.

     

    For exmaple:

    measure =
VAR currdate =
    MAX ( opportunity[date] )
VAR currtype =
    SELECTEDVALUE ( opportunity[Type] )
RETURN
    CALCULATE (
        MAX ( Table[Conversionrate] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Startdate] ) = YEAR ( currdate )
                && [isocode] = currtype
        )
    )

    If above not help, please share some sample data for test.


    Regards,

    Xiaoxin Sheng

    • Andmi's avatar
      Andmi
      Frequent Visitor
      7 years ago

      Hi Anonymous  Thank you for your reply, but I need something a bit different:

       

      I have the table "xyz_datedconversionrate"s with the conversion rates, updated once or twice a year. I want to use only the active ones.

       

      As you can see in the image above the column names are:

      • isocode : The currency code
      • startdate : When the start date is 2019 is an active one
      • conversionrate: The conversion rate, DKK is 1.

       

      I have another table called "xyz_opportunities" where I have different close dates for opportunities. And each opportunity has a value for weighted MRR in its own currency (EUR, DKK, USD, NOK....).

       

      Here the column names are:

      • currencyisocode: The opportunity's currency
      • weighted_mrr_c : the value of the weighted mrr
      • close_date : the date when the opportunity is closed, alternatively I could use a field called "fiscalyear" that cointains only the year

       

      I would like to be able in all my report components to show the weighted MRR values converted in DKK (it is the default currency with 1 in the conversion rate table).

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Andmi ,

         

        According to your discretion, you stored fiscal date in your opportunities table. Conversion table stored currencies value based on calendar date, I can't direct use fiscal to calculate with dax formula.

         

        Can you please share a part of sample data for these tables and the fiscal calendar logic?

         

        Regards,

        Xiaoxin Sheng