Convert value from different currencies

[Andmi]

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?

[v-shex-msft]

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]

Hi @v-shex-msft  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).

[v-shex-msft]

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