Calculate measure with slicer determenating relationship

[K13050]

Hi friends,

Need help with a measure. Need to calculate a value, and using the slicer (date) to determine what to calculate/relationship.
I have 2 companies, showing value in local currency, EURO respectively US Dollars.
Now I want to recalculate the local values so I can see them in the same currency, which is US DOLLARS. And of course there are different exchange rates depending on the period/date.
I have a table with exchange rates, which is on monthly basis. Normally I would just use a RELATIONSHIP and say that all transaction posted in January will be converted using the exchange rate for January. All posting for February use the exchange rate for February and so on…

But that is not how I want it, it is not correct for this purpose.

I want it to convert the local value, ALWAYS using the last date determined by my SLICER.
In my slicer I have the last day of the month, 31st January, 28th February and so on... (12 dates for 1 year)
So if I choose 31st May 2015, then all values until 31st of May should be calculated using Data[LocalValue] * Currency[Average]exchange rate for May 2015.
So a posting in February will still be recalculated with May’s exchange rate. If I choose January-May it still uses May’s exchange rate. Basically always use the LASTDATE exchange rate determined by my Slicer.

In my DATA table I have these columns: Date; Account; CompanyNo; LocalValue;
Currency Table: Date; Average; Currency
Company Table: CompanyName; CompanyNo; Currency
Calendar Table: EveryDay; LastDayOfMonth
Colors show relationships

Created 2 measures and need your help with the last:
=LASTDATE(Calendar[LastDayOfMonth]) used for SLICERDATE
=SUM(Data[LocalValue]) need to use for the calculation described above??

=USCurrency:??????

All help very appreciated. Can't attach yet
Thx

Using Swedish Excel 2013!

 

[SimonNU]

I think you want a measure to be calculated based on what you have selected in a slicer? e.g. if you select "May" then the measure will lookup the exchange rate for "May".

If so, you're looking for a disconnected slicer:
Moving Averages Controlled by Slicer - PowerPivotPro

There are other probably more elegant ways to do it but this is conceptually simple to follow.