Translate foreign currency transactions based on selected date

[CMarsh]

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!

[Anonymous]

@parry2k 
@CMarsh 

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...

[parry2k]

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

[CMarsh]

@parry2k  Thanks - link below:

Sample Data

[parry2k]

@CMarsh 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]]]
)

[CMarsh]

@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.

[parry2k]

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

[CMarsh]

@parry2k  Yes 🙂 I did say I was new to DAX

[parry2k · Accepted solution]

@CMarsh 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 )

[CMarsh]

@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:

[parry2k]

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

[CMarsh]

@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?

[CMarsh]

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

[parry2k]

@CMarsh 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.

[CMarsh]

@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 )

[parry2k]

@CMarsh so it is resolved or what?

[CMarsh]

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

[CMarsh]

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