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 ...
Need Help
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