Monthly Forecast in local currency conversion to EUR

[Thiago_S]

Hi All,

I would your opinion on a task at work, currently we have our monthly forecast (New Orders, Revenue, EBIT, etc...) prepared and analyzed in excel, but I`m planning to start using Power Bi to automate this process. The issue or dilemma that I have is that our forecast is prepared in local currency (CAD and USD) and we need to send in EUR to corporate department and each forecast version/every month we have a new exchange rate to used for our fiscal year.
From my perspective there are 3 ways to have this conversion completed first, in excel before the load which is more manual, second on power query loading with the forecast table and third using dax formulas.
I would to know your perspective on what would be the best practice in this case.

Thanks!

[amitchandak · Accepted solution]

@Thiago_S , we usually have three columns/measures, one each currency and then switch them as per need

few examples

https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-...
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi

[FreemanZ · Accepted solution]

hi @Thiago_S 

Strongly suggest to do it in Power Query. 

It it quick, easy to refresh, good for future adjustment. 

Excel is not suggested, as you need to adjust it in every file every time. Unlike Powwer Query working on preview data(first 1000 rows) in the viewer, Excel do the calculation for every single cell. If you have large tables, that would be a nightmare. 

In DAX, calculated columns is suggested only when other ways are impossible, as it consumes precious memory instead of storage.

[amitchandak · Accepted solution]

@Thiago_S , we usually have three columns/measures, one each currency and then switch them as per need

few examples

https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-...
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi