Forum Discussion
Lookup Price within Date Range
You may try this:
Add an Index Column in the Cost Price table post sorting it on Item Number & Activation Date
Add two calculated columns
For Till Date:
Till Date = VAR _TillDate = LOOKUPVALUE(dtCostPrice[Activation date],dtCostPrice[Item Number],dtCostPrice[Item Number],dtCostPrice[Index],dtCostPrice[Index] + 1) VAR _Result = IF(ISBLANK(_TillDate),TODAY(),_TillDate) RETURN _ResultFor Cost Price:
Cost Price = CALCULATE( MAX(dtCostPrice[Cost Price]), FILTER( dtCostPrice, dtCostPrice[Activation date] <= dtSales[Delivery Date] && dtCostPrice[Till Date] >= dtSales[Delivery Date] && dtCostPrice[Item Number] = dtSales[Item Number] ) )Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
Hi RemonKissen ，
What [from date] and [To date] did you want? If possible, could you please explain it in details? By the way, if possible, could you please inform me more detailed information(such as your expected output)?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello dax Zoe Zhi,
Thank you for your quick response. I hope I make my example clearer, I my example I expect that:
Item Number 1000, Cost 31.98, Activation date 18-02-2020 is active until the next update. In my example this should be active till 01-03-2020. On 02-03-2020 a new cost price is active for Item Number 1000. I added an additional column, till which date the price is active. Do I have to add an additional column? If so how do I have to do it with the Query Editor.
If a sales delivery occurred for item number 1000 on for example date 19-02-2020 than the cost price should be visible in the fact table “New Column”.
Thank you,
Remon
Table Cost Price
| Was Activation date | New Column Added | ||
| ItemNumber | CostPrice | From date | To date |
| 1000 | 31,98 | 18-2-2020 | 1-3-2020 |
| 1000 | 32,17 | 2-3-2020 | 8-3-2020 |
| 1000 | 31,16 | 9-3-2020 | 10-3-2020 |
| 1000 | 34,00 | 11-3-2020 | Till next update… |
| 2000 | 31,98 | 18-2-2020 | 8-3-2020 |
| 2000 | 32,25 | 9-3-2020 | 10-3-2020 |
| 2000 | 32,50 | 11-3-2020 | Till next update… |
| 3000 | 31,11 | 18-2-2020 | 1-3-2020 |
| 3000 | 31,15 | 2-3-2020 | 8-3-2020 |
| 3000 | 31,25 | 9-3-2020 | Till next update… |
Fact Table Sales transactions
| New Column | |||
| Delivery date | Item Number | SalesQuantity | Cost Price |
| 18-02-2020 | 1000 | 10 | 31,98 |
| 19-02-2020 | 1000 | 10 | 31,98 |
| 20-02-2020 | 1000 | 10 | 31,98 |
| 09-03-2020 | 1000 | 10 | 31,16 |
| 10-03-2020 | 1000 | 10 | 31,16 |
| 18-02-2020 | 2000 | 10 | 31,98 |
| 19-02-2020 | 2000 | 10 | 31,98 |
| 11-03-2020 | 3000 | 10 | 31,25 |
| 12-03-2020 | 3000 | 10 | 31,25 |
| 13-03-2020 | 3000 | 10 | 31,25 |