topic Re: Translate foreign currency transactions based on selected date in DAX Commands and Tips

Translate foreign currency transactions based on selected date

Anonymous — Tue, 21 May 2019 12:44:59 GMT

Hi,

We have a model that features several difference currencies stretching back over the last 5 years or so and I have a requirement to display these transactions in GBP at a given date.

The data model consists of a fact table which contains the list of transactions and two dimension tables - one for dates and the other for FX rates.

What I need to be able to do is pick dates from the calendar and the model to then convert all foreign currency transactions into GBP based on the date selected. For example, if I select all transactions up to the end of April 2019 then I need the model to sum all transactions by currency and then pick the prevailing FX for each currency for April 2019. Likewise, if I selected July 2018 then I would need it to pick up all relevant transactions and convert at the July 2018 rate.

Its also worth noting that whilst the transactions are booked up on a daily basis, there is only one FX rate for each month.

I'm farily new to DAX so looking for some advise please! Screenshots of data model uploaded below.

Thanks in advance!

Re: Translate foreign currency transactions based on selected date

parry2k — Tue, 21 May 2019 13:08:56 GMT

Anonymous Can you please share sample data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

Re: Translate foreign currency transactions based on selected date

Anonymous — Tue, 21 May 2019 13:52:07 GMT

parry2k Thanks - link below:

Sample Data

Re: Translate foreign currency transactions based on selected date

parry2k — Tue, 21 May 2019 14:12:30 GMT

Anonymous add following column in your fact to get FX rate of that month and then it is pretty easy from there. You don't need relationship between DIM_RATES and FACT tabe

FxRate = 
LOOKUPVALUE(
    DIM_RATES[FX Rate],
    DIM_RATES[Period],
    EOMONTH( FACT_TRANSACTIONS[[PostingDate]]], -1 ) + 1,
    DIM_RATES[Currency],
    FACT_TRANSACTIONS[[Currency]]]
)

Re: Translate foreign currency transactions based on selected date

Anonymous — Tue, 21 May 2019 14:29:35 GMT

parry2k Thanks for your help but unforunately that doesn't quite give me what I need.

To clarify my requirement, if I select all transactions between Jan - Apr 2019 then I need the model to sum all transactions in their original currencies and then convert the total using April 2019 rates. Taking the example further, if I then selected all transactions between Jan and Feb then I'd need the model to sum all transactions between those dates in their base currencies and convert at the Feb rates.

Below is an example of what I'm looking to achieve using the sample data. The data is filtered for all transactions between Jan - Mar for CNY currency, the FX rate is the Mar CNY rate and the GBP Value column is just column O divided by the FX rate.

Re: Translate foreign currency transactions based on selected date

parry2k — Tue, 21 May 2019 15:02:25 GMT

Anonymous thst is even easier, basically you are saying use the most recent selected month for fx rate not the month in which transactoin happened.

Re: Translate foreign currency transactions based on selected date

Anonymous — Tue, 21 May 2019 15:04:16 GMT

parry2k Yes :) I did say I was new to DAX

Re: Translate foreign currency transactions based on selected date

parry2k — Tue, 21 May 2019 16:34:32 GMT

Anonymous use following expression to add as measure

FxRate as Measure = 
VAR __month = CALCULATE( MAX( 'Date'[Date] ), ALLSELECTED( 'Date'[Date] ) )
VAR __firstofMonth = EOMONTH( __month, -1 ) + 1
VAR __currency = MAX( FACT_TRANSACTIONS[[Currency]]] )
RETURN
CALCULATE( MAX( DIM_RATES[FX Rate] ), DIM_RATES[Period] = __firstofMonth, DIM_RATES[Currency] = __currency )

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 08:39:48 GMT

parry2k I created a new measure and it correctly returns the rate if I select all April dates, however when I select March I don't get the March rate:

Re: Translate foreign currency transactions based on selected date

parry2k — Wed, 22 May 2019 09:52:10 GMT

Anonymous I'm not sure wht is going on there, I tested on my end on test data you provided and it works fine.

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 13:02:44 GMT

parry2k thats odd - I had to make a small change to the code to resolve an error, but I don't think it would be causing an issue:

=
VAR __month = CALCULATE( MAX( DIM_CALENDAR[Date] ), ALLSELECTED( DIM_CALENDAR[Date] ) )
VAR __firstofMonth = EOMONTH( __month, -1 ) + 1
VAR __currency = MAX(FACT_TRANSACTIONS[Currency] )
RETURN
CALCULATE( MAX( DIM_RATES[FX Rate] ), DIM_RATES[Period] = __firstofMonth, DIM_RATES[Currency] = __currency )

Re: Translate foreign currency transactions based on selected date

parry2k — Wed, 22 May 2019 13:22:49 GMT

Anonymous so it is resolved or what?

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 13:24:01 GMT

parry2k no its still not working, just showing the changes I made to the code in the off chance you saw an error

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 13:40:39 GMT

parry2k each of the variables are pulling through the correct data, it seems to be the formula that's causing the issue:

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 13:55:34 GMT

parry2k I've just tried the measure on the sample data I shared and it works, but not on the live data. The only difference between the two is that the live data contains transactions going back over several years, so there is older data in the fact table and the calendar obviously has more dates too (the FX table is identical). That said, having gone through the formula behind the measure I'm not sure what difference this would make?

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 22 May 2019 14:01:56 GMT

parry2k I dropped the DIM_RATES table and re-imported, set the relationships again and its working perfectly now. Thanks a bunch!

Re: Translate foreign currency transactions based on selected date

parry2k — Wed, 22 May 2019 14:04:03 GMT

Anonymous weird, anyhow there was nothing much I could do , sometime it is hard to figure out without looking at real model, glad it worked out.

Re: Translate foreign currency transactions based on selected date

Anonymous — Wed, 08 Sep 2021 15:09:15 GMT

parry2k
Anonymous

what was the final DAX pattern to get the right rate for each selected month ?
I have a similar issue

https://community.powerbi.com/t5/Desktop/Translated-value-calculation-with-currency-rate-on-separate/m-p/2064585#M770468