Calculating selected currency conversion on historic data

[Anonymous]

I've got a fairly complex problem to solve and i can't think of the right approach.  I have a list of historic financial data, broken down into months.  I have a list of currency conversation rates to be applied to each currency for each month.  The challenge i've been faced with is creating a report with a currency selector that can show financial data of multiple months.

I've been able to solve how i'd use a currency selection: I could filter the currency table by the selection and then my conversion measure is simply an aggrigate of what rate is left over.  If i was only showing a single month, this would be easy since that table could be filtered by both Month and Currency leaving me a single record to use within my measure calculation.  The solutions i keep coming to would require Power BI to allow me to calculate a row by row value but using the report filters.

I've tried attacking this problem with my limited statistics math skills, such as considering averaging the currency conversation rates, but nothing i could think of would get the correct answer.

Am i just asking too much of Power BI, or is there a clever trick i've not thought of?

[v-shex-msft]

Hi @Anonymous,

Based on your description, you want calculate conversion based on choose item on slicer,right?

If this is a case , you can use var function to get the current currency rate first, then calculate with this value.

For example: measure.

Result=
var currCurrent =LASTNOBLANK(Table[Currency],[Currency])
var currDate=Max(table[Date])
return
Max(Table[Amount]) * LOOKUPVALUE(Table[Rate],Table[Date],currDate,Table[Currency],currCurrent)

If above formula is not help, can you please share some samle data?

Regards,

Xiaoxin Sheng

[Anonymous]

Close, and here is where the trick comes in.  Each month has a different currency conversion rate because those are real world figures.  Our finance team settles on the rate to use each month for the monthly figures (i'm unsure how they do it, just that i have those figures).  So if you convert historic data using the current rate, you will get the wrong result.  The only way to get the correct result would be to calculate on a row by row basis and apply it that to row's value.

The problem to me is it seems i need to do a Column style calculation but with the flexibility of user selection like a Measure.  I'm leaning on that i might have to use a column and pre-built different dashboards using different currencies.

[GilbertQ]

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

[Anonymous]

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?

[Anonymous]

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.