Forum Discussion
Calculating selected currency conversion on historic data
Super UserHi Anonymous
If I understand your current issue is that you have got details of the currency conversion for each month for each currency.
Then you have got your data in which you have also got the currency and by date (month).
What if you had to bring in your currency conversion for each month into a table.
Then in your data table, you create a new column which will be the Month & Year combination from your date. (The reason for your Month & Year combination is so that you can have it for multiple Years).
You then create the same column in your Currency Conversion table where you have the Month.
Then what you could potentially do, is to them Merge the tables together matching them on the Currency and Month & Year columns to join on. In doing it this way you should then have your Currency conversion on each row, for each currency?
After that you would then have the data stored in the format required and be a lot easier to apply your slicers and measures?
Hi GilbertQ thanks for your suggestion. To give some clarity on the current data set i'm working with: I have a date dimension table and each value in my financial table has the date of the 1st of the relevant month and the currency conversion table also has the same date style. I'm able to link those two tables together via this Date table making that bit nice and easy. I have also been able to successfully display the correct currency conversion, based on user selection, as long as i am only selecting a single month of data and single currency on my slicers.
If i'm reading your suggestion correctly, you are suggesting i merge down the current currency and its "convert to" value into each possible currency one column at a time. There is 197 currencies in my data. meaning there would be a 197 values on each data row? Presently the financial data is comprised of just shy of 1 million rows, which covers data from January 2012 until Feb 2017.
So, lets say i've produced my financial table with the 197 currency converted columns. How do i create a reporting element that shows the data for the selected currency on a column basis? Would i be creating a different dashboard for each currency?
- GilbertQ
Hi Anonymous,
Just so that I understand it correctly, you want to provide an option for the consumer of the report to be able to select any Source Currency and any Destination Currency and then see this over time?
It is indeed a bit tricky, but I have found over time using Power BI and the Query Editor is that there is a way, you sometimes have to be creative in how you get to the solution.
Do you perhaps have a small sample of data, where you have got the data table and the Currency Conversion table?
Hi GilbertQ, yes you are getting pretty close. The source currency is already stored on the financial data lines (its data for global sites). The user would simply select 'Date Range', 'Display Currency' and 'Sites to include'. For simplicity sake, lets assume that users will only select sites with the same base currency (lets not complicate it).
I do have some sample data, i've even compiled a demo file to attempt solving this problem. I might need to scrub it a bit to remove any identifiable information. What would be the best method of hosting the pbix demo file?
- GilbertQ
Hi Anonymous,
Ok so it would then be the conversion between the Source Currency and the selected Currency? As well as the other attributes, but that should be a lot easier.
If you have got OneDrive Personal, or Google Drive, or Dropbox to share the scrubbed PBIX file.