fixing DAX formula - exchange rate

Hi, 

I need to convert local currency amounts to EUR by using the exchange rate of the selected end-year and the last available month of that year. I have a formula that works, but it does not use the same ex rate for each month: 

first column is a date column from a date table

second is the local currency amount

3rd: result of my Vlookup ex rate conversion dax measure

4th: ex rate test: shows what ex rate the vlookup measure used

the last 2 columns are the result of 2 measures: 

the 2 measures show the same, correct month and year in this table, however in the Vlookup dax measure these values are changing: 

Sell-in EUR Vlookup =

SUMX (

    purchases_by_week_vendor,

    VAR exchRate =

       

            LOOKUPVALUE (

            exchange_rates[avg_rate],

            exchange_rates[source_currency_code], purchases_by_week_vendor[currency_code],

            exchange_rates[year], MAX ( year_selector[year]) ,

            exchange_rates[month], [End month number]

        )

 

RETURN

         [Sell-in local currency (test)] * exchRate

)

 

The issue is, that the year_selection table is connected to the date table. I cannot inactivate that relationship as the datamodel is used for several reports and I cannot change that relationship. 

As I see the formula is correctly choosing the year, but the month is not consistent in the monthly breakdown. 

 

If selected year is 2022, latest available month in the fact table is November, so the 2022 nov ex rate should be used for each and every month. 

 

 

Any idea how I could fix this formula?

thank you !