Converting ending balance using exchange rate based on period selection
Both Fact_GL and Dim_ExchangeRate comtains multiple MonthEndDate for a type of currency. I want to create a measure where the Rate from Dim_ExchangeRate will be picked up based on Period Selection. The rate will be used to divide the ending balances which is also based on Period Selection (Converted Amount). For example, when May-21 is selected, YTD May-21 numbers will be divided by the May-21 rate and when Jun-21 is selected, YTD Jun-21 numbers will be divided by the Jun-21 rate.
The Final Amount should also include if the Revalue Type in Fact is "Revalue", then take the Converted Amount else Base Amount.
I'm unable to create calculated column as the rate should not be stagnant so a measure is considered.
Final Amount for each row in Fact based on period selection:
In your measure, "[CurrencyKey] = _CurrencyKey" might not be correct as it will fix the rate for each row in Fact table. What I require is if May-21 period is selected, I need YTD May-21 Trans Amount to be converted using May-21 rate and if Jun-21 period is selected, YTD May-21 + Jun-21 entries will be converted using Jun-21 rate.