Adjust Exchange Rates based on user-selected period

[ElenaGrom]

Hello, Community!

I have the requirement to:

 1) calculate the YTD Sales amount in Local currency based on the last reporting month, selected by the user (this one is done)

 2) Where my problem is: recalculate the result from Step 1 into Reporting currency (EUR), but with the Exchange Rate valid on the month, which the user marked as "last reporting".

E.g.,

if the user selects "March 2022", the YTD period is 01.01.2022-31.03.2022, the historical Exch Rate should be taken as of 31.03.2022.

if the user selects "February 2022", the YTD period is 01.01.2022-28.02.2022, the historical Exch Rate should be taken as of 28.02.2022.

I have:

- Fact table, which contains amounts in local currencies, transaction dates and currency codes. 

- Exchange Rates table with currency codes and historical exchange rates

- Date slicer, where the user is asked to specify the "last reporting month". (First reporting month is always January of the selected year). From that selection I derive via measures:

Sales Year to Date: 

SalesYTD = CALCULATE(sum('Fact'[Sales Local Currency]),DATESYTD('Calendar'[Date]))

Adjusted Rate: 

AdjustedRate = calculate(sum(ExchRates[Rate]),ExchRates[Currency] in values ('Fact'[Local Currency]))

EUR Amount:

EUR Amount = [SalesYTD] * [AdjustedRate]

And display them in matrix visual. 

Example 1: Reporting month = February. Everything works fine

Example 2: Reporting month = March.

Because there are no sales data in March from India in INR, the AdjustedRate is also filtered out, though the INR Exchange rate exists for March in Exchnage Rate table. As a result, the EUR Amount is not calculated, although the SalesYTD value is > 0.

I need to find a way to apply the INR rate from March, and thus calculate the EUR Amount for India.

I was trying differnet combinations of FILTER on AdjustedRate measure, but none seem to work, or maybe I am doing something wrong.

Your help will be highly appreciated!

Thank you in advance!

PS: here is the link to PBIX file:
https://drive.google.com/file/d/1BVDWF1Y23jZhNwMnqHPcPvLpnPusclho/view?usp=sharing