DAX to convert currency?

[sgannon1]

Hi all,

My reporting needs to be captured in one currency - EURO. One of my tables is in GBP so I need to convert everything in this table to EURO and then to append this with my EURO table. 

I have no experience with DAX but have been told I may need a DAX forumula to convert. Can anybody help?

Thanks in advance. 

[Eric_Zhang]

@sgannon1

I have no idea on what exactly your scenario is, however I think you need an exchange rate table for whatever cases. Please check the below sample.

Table = SELECTCOLUMNS(FILTER(CROSSJOIN('exchange table','sales table'),'exchange table'[currency]='sales table'[currency]&&'exchange table'[date]='sales table'[sales date]),"sales date",'sales table'[sales date],"GBP",'sales table'[sales],"ERU",'sales table'[sales]*'exchange table'[exchange rate],"exchange rate",'exchange table'[exchange rate])

If you have any question, feel free to let me know.

[trebgatte]

I agree with Eric, though I'd externalize the conversion table for easier maintenance. Most corporate finance groups have a fixed conversion rate for a time period rather than using the market rate. 

Perhaps you can put this in a centralized XLSX or a SharePoint list so that it can easily be updated without having to do anything to the PBIX.

Treb Gatte | Business Solutions MVP | Blog | Twitter

[sgannon1]

Thanks for your responses - below is the table, the Amount column is the one I need converted as it is currently GBP (my reporting needs to be in Euro), and the table below it has the currency conversion rate.

I'd appreciate any help on the how to construct the formula to do the conversion?

[achinm45]

Hi @sgannon1

You may need an intermediate table for currency conversion because 1 GBP is not equal to 1EUR.

Then with help of relationships you can achieve .

Do provide sample data to make things clear.