Dynamic dimension table depending on the Date

[Bastienlin_]

Hello,

I have an issue where my dimension table is changing over the years. What I tried to do to deal with it, is to add a date to all my tables so that I can filter the right lines in my dimension table. Here is a sample of my data:
I have no "Orange Juice" in 2022 in my ref_Company because it isn't in our scope anymore, but it is still here in my fact table (Price):
Ref_Company(Dimension table):

Price(Fact table):

Ref_Date(Dimension table):

What i expected is that, when I select 2022, we can only see Coca and Pepsi on my visual. But here is what I have:

It seems that is still manage to get the Orange_Index from 2021 in my Ref_Company. So my ref_Company isn't dynamic.
My goal is to have my Ref_company dynamic depending on the date I select on my slicer.
Here is the dropbox link to my pbix file (I can not import my pbix file on the forum i don't know why):
https://www.dropbox.com/s/t58ujxuo1ppl2bm/Dynamic%20dimension%20table%20depending%20on%20the%20Date....

Do you know how can I deal with this issue ? (Note that I have a lot of facts table like my Price table, so I can not just delete the lines about Orange Juice.
And if you think that my solution about adding a date to my Ref_company isn't a good one, feel free to explain another solution to me.

Thank you very much for your help !

[v-easonf-msft]

Hi, @Bastienlin_ 

Please try following formula to replace your original value:

M_value(wrong total) = CALCULATE(SUM('Price'[Price]),FILTER('Price','Price'[Date] in VALUES(Ref_Company[Date])))

M_price(correct total) = SUMX(VALUES('Price'[Index]),[M_value(wrong total)])

result:

Best Regards,
Community Support Team _ Eason