Keeping a fixed conversion rate while comparing current and previous year

[akim_no]

I have a semantic model where my fact table is linked to my calendar table by date. I use a month/year slicer to allow the user to select a single month and year (e.g., November 2025). Based on this selection, I retrieve a conversion rate for that month, which should remain fixed and be applied to all relevant data.

Here’s how I calculate the rate and sales:

 

SelectedRate =

VAR SelectedMonth = FORMAT(MAX('Calendar'[Date]), "MM/yyyy")

VAR SelectedCurrency = MAX('FactTable'[CurrencyId])

RETURN

CALCULATE( MAX('RateTable'[RateValue]),

TREATAS({SelectedCurrency}, 'RateTable'[CurrencyId]),

TREATAS({SelectedMonth}, 'RateTable'[MonthYear]) )

SalesWithRate =

VAR AggTable = SUMMARIZECOLUMNS(

'FactTable'[MonthYear], 'FactTable'[CurrencyId],

"LocalRev", SUM('FactTable'[LocalSales]) )

VAR AggWithRate = ADDCOLUMNS(

AggTable, "Rate", CALCULATE( [SelectedRate],

ALLEXCEPT('FactTable', 'FactTable'[MonthYear],

'FactTable'[CurrencyId]) ) )

RETURN SUMX( AggWithRate, DIVIDE([LocalRev], [Rate]) )

SalesPYWithRate = COALESCE( CALCULATE([SalesWithRate],

SAMEPERIODLASTYEAR('Calendar'[Date])), 0 )

The measures work fine for the current year: the selected month’s rate is applied and the cumulative sales are correct.

The problem: When comparing the current year with the previous year using SAMEPERIODLASTYEAR, the conversion rate is also shifted to the previous year. For example, November 2025’s rate is applied to 2025 data, but November 2024’s rate is applied to 2024 data. The goal is to keep the rate fixed according to the slicer selection, regardless of the year being analyzed.

The difficulty is that the calendar-fact table relationship imposes a date context, and I need the measures to use both the analysis date and the fixed conversion rate. I’ve tried REMOVEFILTERS, ALL, ALLSELECTED, and ALLEXCEPT, but I haven’t been able to fix the rate while keeping correct time intelligence calculations.

Question: How can I keep a fixed conversion rate based on the slicer while still using time intelligence functions like SAMEPERIODLASTYEAR for year-over-year comparisons?

[akim_no]

In the end, to avoid the rate being impacted by SAMEPERIODLASTYEAR, I first calculate the PY amount in local currency. Then I apply the conversion, instead of relying on the already converted amount and using time intelligence functions. This approach fixes the issue.

[v-hjannapu]

Hi @akim_no,
Thanks for the update. Glad to hear the issue is resolved.
Please feel free to reach out if you need any further help.

Regards,
Community Support Team.

[lbendlin]

Based on this selection, I retrieve a conversion rate for that month, which should remain fixed and be applied to all relevant data.

Why does the user's choice influence the FX rate? Shouldn't that rate be immutable?

For example, November 2025’s rate is applied to 2025 data, but November 2024’s rate is applied to 2024 data. The goal is to keep the rate fixed according to the slicer selection, regardless of the year being analyzed.

Can you provide some more context on why you would want to do that?

The problem with the Year-over-Year comparison is that you cannot guarantee that all transactions were conducted in the same currency in both time intervals. 

Why does the Order_line table have the currency code AND the order date column?  These two columns should be in the Order table only.

Finally - your sample data doesn't have the required coverage.  Each currency appears only in one year but not the other

[v-hjannapu]

Hi @akim_no,
Thank you for sharing the PBIX. Since the report contains many measures and dependencies, instead of changing anything directly in your file, I first reproduced your scenario in a very small sample PBIX with just a few rows.

This made it easy to validate your requirement:
  1.The conversion rate is taken only from the period selected in the date slicer.
  2.That same fixed rate is applied when using SAMEPERIODLASTYEAR.
  3.so the PY calculation no longer shifts to a different rate.

The sample confirmed that this logic works as expected.

Please let me know which measure you want us to adjust first.

Regards,
Community Support Team.

[akim_no]

@v-hjannapu, 

I think it worked for you because you are using a single currency. However, when working with multiple currencies and testing it on my data, it doesn’t work.

Even for the current value — the value of n — it doesn’t give the correct conversion.

In my file, what I’m trying to do is create the following measures ; 

- Euro Amount,

- SelectedRate,

- PY Euro Amount.

For the other ones, however (SelectedRate for test, PY Euro Amount choisen rate, Euro Amount Choisen rate), it allows me to test whether the conversion is applied using the rate I want to use.

Thank you 

[v-hjannapu]

Hi @akim_no,

As suggested by @lbendlin , could you please share the exact sample data? This will help us reproduce the issue on our side and provide the correct answer as per your requirement.

Regards,
Community Support Team.

[v-hjannapu]

Hi @akim_no,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[burakkaragoz]

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.

[akim_no]

@burakkaragoz , I tried that suggestion, but it doesn’t really work, it still causes the same problem.

https://github.com/akimno/power-bi/blob/main/order_test.pbix

[akim_no]

@lbendlin , A PBIX file is available at the link below to explain this case in more detail : 

https://github.com/akimno/power-bi/blob/main/order_test.pbix
Thanx

[lbendlin]

REMOVEFILTERS or Window functions should work.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...