Extracting date range from excel file and use them for calculations

[Janica123]

Hi everyone, 

I have an Excel File with the name Pricelist_01.01.2022-31.12.2023.xlx that is stored in a Sharepoint. This file contains product prices which are only valid for the period from 01.01.2022 to 31.12.2023. The data in the file looks like this:

I have a Power BI Sales Report, that shows the sales generated from the products.

I now need to implement a logic so that only the prices of the products contained in the excel with the name Pricelist_01.01.2022-31.12.2023.xlx are used for the sales calculations in the period from 01.01.2022 to 31.12.2023.

As soon as a new Excel with a new period (e.g. Pricelist_01.01.2023-31.12.2024.xlx) is loaded into the sharepoint, the old prices should no longer be used for the calculations in the Sales Report, but the new ones that are valid from 01.01.2023 to 31.12.2024. However, this only applies to the period, everything before the period should not be overwritten. Meaning the Calculations for eg. March 2022 must continue to be based on the prices from the Pricelist_01.01.2022-31.12.2023.xlx. 

Is there a possibility to implement such a logic? 

Many thanks in advance!

[mickey64 · Accepted solution]

For your reference.

Step 1: I add a "FY" column and "Key column" below.

"Pricelist_01.01.2022-31.12.2023.xlx"

Step 2: I add a relationshop between "Pricelist Table" and "Sales Report Table" with "Key column".