Forum Discussion

Aleli's avatar
Aleli
Frequent Visitor
9 years ago
Solved

dynamic computation based on slicer

Hi,   Can anyone suggest a simpler way to show converted amount to USD?   Currently, here's what I have: Table 1 - COUNTRY, YEAR, MONTH, INCOME (in local currency) Table 2 - YEAR, MONTH, LOCAL ...
  • MattAllington's avatar
    9 years ago

    There are a few approaches, depending on your data.

     

    If you have monthly data for all months and every month as exchange rate data too, you can do this.

     

    create a calendar table that contains the months (with a unique ID)

    Join the 2 data tables (Table 1, Table 2) to this one common calendar table.

     

    When you filter from the calendar table by month, both table 1 and table 2 will be filtered.  You can then "harvest" the exchange rate with something like this

     

    Rate To Use = Max(Table2[Exchange Rate])

     

    As long as you only have every month covered once and always once, this formula will give you the exchange rate.

     

    To work out the conversion, you could use something like this

    Income Local Currency = sum(table1[Income])

    Converted Rate = sumx(Calendar,[Income Local Currency] * [Rate To Use])

     

    For the time intelligence needs, take a read of the article I posted this week here http://exceleratorbi.com.au/dax-time-intelligence-beginners/