Convert various currencies to GBP based on latest available exchange rates

DAX DAX Calculations
1

Hi all

I have researched a lot and struggling with this one, I wonder if you can help.

Scenario is:
Exchange Rate Table “ExRates” has Code, Date, ToGBPRate

Each currency (USD, and 70+ others) has rates, usually we are downloading a daily set of rates. But, for some currencies we stopped getting rates, or maybe some issue arose, and the latest rate maybe is a month ago.

I want to convert the currency bank account amounts back to GBP to assess “what are they worth in terms I understand” but I ALWAYS want this at the latest rate. That is, for example, I want today’s USD balance calculated at today’s rate. Not the date of the bank posting or some other “transaction” date. Always at latest rate available for that currency in the ExRates table.

All the exchange rate examples I found using LOOKUPVALUE seem to use rate at Order Date, Sale Date, or something.

I would like to do something like:

LOOKUPVALUE(ExRates[ExRateAmount],
ExRates[Code],BankEntry[Code],
ExRates[Date],"MAX of the ExRates[Date] for that particular ExRates[Code]"
)

I am not averse to making a table each day of the latest currency exchange rates separate from the ExRates history table, if you think this would be easier (ideas on how I code that welcome :slight_smile:)

This pattern would apply to Stock Exchange Price histories too, where we don’t always get an instrument price daily for every instrument (e.g. some are priced monthly, some daily etc).

Help?

2

Hello @mattduffyzz,

Thank You for posting your query onto the Forum.

One of our expert Nick had created a bunch of videos based onto this videos about how to handle the currency conversion issue. I’m providing few of the links below of the videos. Also feel free to browse our education portal for more contents available onto this topic.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

3

Thank you for responding rapidly Harsh :+1:

I checked all those videos (already, and blog posts of same thing) and other websites etc. Each one of them ends up using a specific date in the row context where I want the latest date in the ex rates table for the row context currency.

I have now solved this using a summarize command to make a calculated table that gets the last non blank date and rate grouping by currency code.

I suspect I could embed this in my measure but a daily refreshed calc’d table seems a neat and efficient method to me.

Thanks again, I will keep watch and see if someone has a better way, in measure or a more reliable calculated table method.

Thanks.

Matt

4

Hi @mattduffyzz , did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

As explained, @Harsh information didn’t address my particular requirement, and I’d already been down that path: it’s much easier to get an exchange rate for a particular date (like order date for example) than it is to say get me the latest rate for a particular currency from the rates table (where we might not get rates every day for every currency).
CurrCode,RateDate,Rate
EUR, 24 Feb, 1.2066
EUR, 23 Feb, 1.2045
USD, 23 Feb, 1.4002
USD,19 Feb,1.3809

In this case, if I am converting a USD bank account balance (regardless of when that cash amount was posted in my bank ledger) I want it to use the latest rate - i.e. 23 Feb for USD. But for EUR bank accounts, I want to use the 24 Feb rate.

In SQL I would be able to create a view to identify an individual latest rate in seconds, or a view of latest rate for each currency. But, of course, DAX is different :slight_smile:

So, I had some fun achieving this in DAX, and think I have it, but suspect there is an easier way!! Here is my DAX code to make a table of latest rates - constructive advice on improving this is welcome :slight_smile:

SUMMARIZE(‘ExRates’,
‘ExRates’[CurrCode],
“Latest Rate Date”,LASTNONBLANK(‘ExRates’[RateDate],‘ExRates’,[RateDate),
“Latest Rate”,
CALCULATE(FIRSTNONBLANK(‘ExRates’[Rate],1),
LASTNONBLANK(‘ExRates’[RateDate],‘ExRates’[RateDate])
)
)

Since the CALCULATE filter (LASTNONBLANK) should return only one row, the FIRSTNONBLANK isn’t strictly necessary I could use any function - this could be SUM, MAX, AVERAGE … anything, since it should just be one value (there can be one Rate per Rate Date per Currency Code).

6

Hi @mattduffyzz - I have gone through your requirement and do think that below links provided by @Harsh responds to same problem of missing Currencies for some date and using for Latest available date.

If these are still not helpful then please share a sample PBIX file for us to help further.

Thanks
Ankit Jain

7

Hi @mattduffyzz , did the response provided by @Ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @mattduffyzz, we’ve noticed that no response has been received from you since the 26th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @mattduffyzz, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!