Keeping a fixed conversion rate while comparing current and previous year

Hi akim_no ,

You have correctly identified the issue: SAMEPERIODLASTYEAR shifts the date context for everything inside the CALCULATE function, including your SelectedRate calculation.

When you calculate PY Sales, the measure looks for the rate in November 2024, but you want it to keep using the November 2025 rate (from the slicer).

Here is the solution using the "Freeze" technique with ALLSELECTED.

The Solution

You need to modify your rate logic to explicitly ignore the current query context (which might be shifted to last year) and instead look at the original slicer selection.

Create a new version of your rate measure (or update the existing one) to use ALLSELECTED on the date capture:

FixedSelectedRate = 
-- 1. Grab the date from the Slicer (ignoring the current row context or time shifts)
VAR GlobalSelectedDate = CALCULATE( MAX('Calendar'[Date]), ALLSELECTED('Calendar') )
VAR SelectedMonth = FORMAT(GlobalSelectedDate, "MM/yyyy")

-- 2. Grab the currency from the current iteration (keep this context!)
VAR SelectedCurrency = MAX('FactTable'[CurrencyId])

RETURN
    CALCULATE( 
        MAX('RateTable'[RateValue]),
        -- Apply the locked date from the slicer
        TREATAS({SelectedMonth}, 'RateTable'[MonthYear]),
        -- Apply the currency from the current row iteration
        TREATAS({SelectedCurrency}, 'RateTable'[CurrencyId]) 
    )

How to use it

Now, update your main calculation to use this "Fixed" rate. This will ensure that even when you shift Sales to last year, the Rate logic stays anchored to the slicer.

SalesPYWithFixedRate = 
VAR AggTable = 
    SUMMARIZE( -- Changed to SUMMARIZE as SUMMARIZECOLUMNS can be unstable inside measures
        'FactTable', 
        'FactTable'[MonthYear], 
        'FactTable'[CurrencyId],
        "LocalRev", SUM('FactTable'[LocalSales]) 
    )

VAR AggWithRate = 
    ADDCOLUMNS(
        AggTable, 
        "Rate", 
        [FixedSelectedRate] -- This now always returns the Slicer's rate
    )

RETURN 
    CALCULATE(
        SUMX( AggWithRate, DIVIDE([LocalRev], [Rate]) ),
        SAMEPERIODLASTYEAR('Calendar'[Date])
    )

Why this works:

• ALLSELECTED('Calendar'): This function retrieves the filter context as defined by the slicers on the page, before SAMEPERIODLASTYEAR overrides it with the 2024 dates.

• Context Preservation: By calculating GlobalSelectedDate into a variable using ALLSELECTED, we "lock" the November 2025 date. We then force this date into the rate lookup, regardless of whether the Sales calculation is happening in 2024 or 2025.

Let me know if this works with your specific data model setup!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.