Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculating selected currency conversion on historic data

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?

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        GilbertQ
        Icon for Super User rankSuper User
        9 years ago

        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?