Pick currency rate of max date selected to calculate prior periods

[shashank181993]

Hi Team/ @sunny_talwar ,

I have a Pivot table where I am able to bring the exchange rate but the rate should be applied on the selected end date in the calendar across the rows in the Reporting currency columns.

Example: My end date in the screenshot is 02/13/2021 then the Reporting Currency should be applied as 1.55 for all the rows and if it's 02/14/2021 then 1.4

My current expression is here -

=Sum({<AsOfDate={'=$(vPerfEndDate)'}> } DISTINCT Pick(Match(Fx_CurrencyBK,[Reporting Currency]),ExchangeRate))

what set expression shall be used to apply the currency on the selected end date?

TIA