Forum Discussion
Helper IICalculated Column Based on Two OTHER Related Tables (three tables total)
Hello!
I am trying to connect a sales source to a date table, and a price table. (example below)
I am trying to determine what the unit price was on a given week for a SKU so that I can calculate Net Revenue from a table that is reporting Gross Revenue (the finance logic is calculated in our Finance Pricing Sheet table #3 already).
Table 1 (Data Source):
| Date | SKU Number | Gross Revenue |
| Oct 5, 2022 | 123456 | $2000 |
| Oct 6, 2022 | 123456 | $5000 |
Table 2 (Date Table):
| Date | Week Start | Month Start |
| Oct 5, 2022 | Oct 3, 2022 | Oct 1, 2022 |
| Oct 6, 2022 | Oct 3, 2022 | Oct 1, 2022 |
Table 3 (Finance Pricing Sheet):
| Week Start | SKU Number | Net Revenue Unit Price |
| Oct 3 | 123456 | $20.99 |
| Oct 10 | 123456 | $29.99 |
| Oct 17 | 123456 | $25.99 |
Hi,
Create a relationship (Many to one and Single) from the Date column of the Date table to the Date column of the Calendar Table. Write these calculated column formulas
Week start = =RELATED('Calendar'[Week Start])Unit price = CALCULATE(MAX(Finance_pricing[Net Revenue Unit Price]),FILTER(Finance_pricing,Finance_pricing[SKU Number]=EARLIER(Data[SKU Number])&&Finance_pricing[Week Start]=EARLIER(Data[Week start])))Hope this helps.
9 Replies
dswindenHi ryan_mayu !
I would like to be able to look up the price of an item on a given week. I have hundreds of SKU's and the prices can change week to week depending on price promotions. This is a dumb analogy, but in excel I would do;
sumifs(Price Column, Week Start Column, Week Start Lookup, Sku Number Column, Sku Number Lookup)
OR
What was the price of SKU 123456 on week of October 10th. Answer $29.99- Ashish_Mathur
Super User
Hi,
So in the first table you want to create a calculated column formula which will return the price. Am i right. If yes, then share a calendar table with 2 columns - Date and Week start date (share this for a few months atleast).
