Changing Selling Price Based on Date

[Drece-gkn]

Hello, I have recently spotted an issue within one my applications. 

I have a column that has Selling Price and a column that Confirmation Date. Selling Price can change monthly, so I need the Selling Price to reflect the correct price based on the Confirmation Date column. 

Here is an example to illustrate the issue. Let's say Selling Price for part A in January is $1000 and Selling Price for part A in February is $2000 dollars.  I want that Selling Price column to look at the Confirmation date column and see that Confirmation date was 2/xx/2024 or 1/xx/2024 and reflect those correct prices. I will include some screenshots for aid. 

Here is the excel sheet that pulls the Selling Price Data. This is just an example of one part, but every part is set up the same way. 

This next screenshot is from the table visualization within the application that has the Confirmation Date. 

So basically, what I think at least, I need Confirmation Date column to look at the MonthYear column and compare, then report back the correct Selling Price associated to that month. 

Thank you!

[ali_hijazi]

technically speaking I understand what you want
but the sample data is not informative:
we have two confirmation dates repeated with the same selling price
Ok I assume I take the distinct rows:
but I will end with 2 lines
03/21/2024 1,700
03/21/2024 2,132

so same confirmation date for the same part with 2 different prices

ok out of this sample data what do you want as a result so that I can figure out how to accomplish it

[LRuCelver]

From what you've shared, I assume this is what your data model (at least the relevant part) currently looks like:

Instead of just using the product as a key, you can create a composite key using the Product and MonthYear. In the Sales table you can create a MonthYear from the Confirmation Date:

Now ideally you would be using numeric keys. You can use AutoNumber for that.

[Drece-gkn]

I will give a much specific sample data, filtering it by only 1 part. As you see for this part we have two different Selling Prices. If you look at the Selling Price Excel, the only time the Selling Price should reflect as $7440 is in Jan. So, this Table shouldn't be reading both possible Selling Prices. The Confirmation date is 3/xx/24, so it should only reflect $9466.

Thank you

[LRuCelver]

It looks like the Confirmation Date is not linked to to Selling Price MonthYear.

If you select a single Confirmation Date, how many MonthYears can you see?

[Drece-gkn]

You're right, Confirmation Date comes from a separate data connection while Selling Price and MonthYear are from another Data connection excel sheet. The only link they have is a left join with my other tables and its joined together by Material Number (part). Which I don't think helps associate Confirmation Date and Month Year together! 

So, I selected a single month year, and I am able to just freely filter by whatever month I want. So, for example, I filtered by 3/19/2024 and none of the months in MonthYear Filter Pane were greyed out. So, I can just basically filter by whatever month I want, which is not good. How would I create that link between them?

[LRuCelver]

You can derive a MonthYear from the Confirmation Date field:

Date(MonthStart([Confirmation Date]), 'MMM-YY') as MonthYear

If you now join the tables you can join them on the Material Number and MonthYear, linking the Selling Price to both.

If you instead want to keep them as seperate tables, having both Material Number and MonthYear in both tables will create a synthetic key.
This can be avoided by creating a composite key:

In the selling price table:

[Material Number] & '|' & MonthYear as Key_SellingPrice

In the materials table:

[Material Number] & '|' & Date(MonthStart([Confirmation Date]), 'MMM-YY') as Key_SellingPrice

To help with performance it is recommended to use numeric keys. To make this key numeric, we can use AutoNumber at the end of the script:

AutoNumber Key_SellingPrice;

While you could also use the AutoNumber() function to make the numeric while loading it into the table, using the AutoNumber keyword outside of the table has the advantage that you can comment it, leaving the key in its "raw" form. This makes it a lot easier to debug the keys.