Forum Discussion
dynamic computation based on slicer
- 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/
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/