Currency conversion using DAX

Anonymous — Fri, 26 Apr 2019 08:42:11 GMT

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-using-power-query-and-power-pivot)

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

Re: Currency conversion using DAX

AnthonyTilley — Fri, 26 Apr 2019 11:46:01 GMT

Are you able to share some sample data from each of your tables or a PBIX file

i have seveal reports that have currancy exchange involoved fo muliple exchange rates so i am happy to take a look.

Re: Currency conversion using DAX

Anonymous — Mon, 29 Apr 2019 08:50:11 GMT

Re: Currency conversion using DAX

Anonymous — Mon, 29 Apr 2019 08:52:16 GMT

Hi Anthony, thank you for your feedback. I've posted an screenshot of the queries and relations and some examples. I hope this is sufficient :-)

Best regards

Alex

Re: Currency conversion using DAX

Anonymous — Fri, 10 May 2019 07:41:34 GMT

Hi all, I've found a solution via adding columns with exchange rates to the base table and changing the calculation withing the measure via switch. Not the optimum but it works fine :-)

Thank you & best regards

Alex

topic Currency conversion using DAX in DAX Commands and Tips

Currency conversion using DAX

Re: Currency conversion using DAX

Re: Currency conversion using DAX

Re: Currency conversion using DAX

Re: Currency conversion using DAX