Hi all,
my name is Alex and I'm not new to PowerBI, but quite new to using DAX.
I am trying to set up a currency conversion within my dashboard and the conversion shall be done with daily changing exchange rates for both KPIs.
What I have:
- a huge query from BW (~ 400.000 data sets with two KPI based on calendar days in EUR) (name: MERGEBWDATA)
- a query created out of the BW-Query, to have unique values for date (name: 30Q2AllDates)
- a query representing a download with several exchange rates to EUR (e.g. USD, GBP, NOK) on a daily basis (starting 1995, modified via pivoting, downfill, unpivoting to get the exchange rates for the weekend days) (name: ExChangeRatesFinal)
- a query created out of ExChangeRatesFinal to get the single values for the selected Currency which is stored in the Variable M2_SelectedCurrency
I found several examples how this could work and tried to copy / reconstruct them (e.g. https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...)
but obviously it doesn't work - the displayed values are exactly the same as the base values in EUR.
Furthermore I have no clue until now what I can do to keep EUR as a selected value since it is not in the query with the exchange rates (because it's the base currency)...
The relations are created as within the above mentioned link and the measures are:
- MeasPOVolume = sum(MERGEBWDATA[Net Value (EUR)])
2.MeasPOVolume EX =
IF(HASONEVALUE(CurrencySelector[CurrencySelector]);
SUMX(ADDCOLUMNS(MERGEBWDATA;"EXNet";
MERGEBWDATA[Net Value (EUR)]*LOOKUPVALUE('ExChangeRatesFinal'[ExChangeRate];ExChangeRatesFinal[Date];
MERGEBWDATA[PO Document Date];ExChangeRatesFinal[Currency];CurrencySelector[M2_SelectedCurrency]));[MeasPOVolume])
)
For any hints how I could get things working I would be very grateful.
Thank you in advance and best regards
Alex
