Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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:
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:
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).
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