Forum Discussion

akim_no's avatar
akim_no
Helper III
8 months ago
Solved

Keeping a fixed conversion rate while comparing current and previous year

  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 th...
Need Help
  • akim_no's avatar
    akim_no
    7 months ago

    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.

     

lbendlin's avatar
lbendlin
Super User
7 months ago
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