Lookup Price within Date Range

[RemonKissen]

Hello,

I’am a starter with Power BI, and I’ve got a question. The data in my example is fictional.

In our company we make every 2 weeks a cost price calculation. This cost price calculation is the basis for the calculation of sales prices. In general the sales prices and the cost prices are fixed for a 2 week period. But if a recipe changes it could be possible to receive a new cost calculation, within the 2 week period. The Cost Price is active till the next activation date of that item number.

I need to match de cost price of the product with the delivery date of the transaction table.

This example look a lot like this blog post:

https://community.powerbi.com/t5/Desktop/Lookup-Price-within-Date-Range-by-delivered-Date/m-p/112365...

The only difference is that I don’t have [To Date]. Is it necessary to create in Query Editor a column [From Date] and [To Date]? If so, how do I have to do it. 

Thank you very much,

Remon

Table Cost Price

Item Number	Cost Price	Activation date
1000	31,98	18-2-2020
1000	32,17	2-3-2020
1000	31,16	9-3-2020
1000	34,00	11-3-2020
2000	31,98	18-2-2020
2000	32,25	9-3-2020
2000	32,50	11-3-2020
3000	31,11	18-2-2020
3000	31,15	2-3-2020
3000	31,25	9-3-2020

Fact Table Sales transactions

Delivery Date	Item Number	Sales Quantity
18-02-2020	1000	10
19-02-2020	1000	10
20-02-2020	1000	10
09-03-2020	1000	10
10-03-2020	1000	10
18-02-2020	2000	10
19-02-2020	2000	10
11-03-2020	3000	10
12-03-2020	3000	10
13-03-2020	3000	10

[vivran22]

Hello @RemonKissen ,

You can achieve this using Power Query as well.

I have used Merge Queries: First to get the latest date for each item and then to get the cost of the latest date

Result:

You may find the sample pbix file here

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

[RemonKissen]

Hello @vivran22 Vivek,

Thanks for you quick response. It is close to the solution I am looking for. Could you might read the further explanation which I gave to @dax Zoe Zhi, this includes the required/desired outcome.

Cheers!

Remon

[vivran22 · Accepted solution]

@RemonKissen 

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
_Result

For 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

[Solvera]

When adding the Till Date measure to the Cost Price table where the Item Number is alphanumeric and not just numeric, will the LOOKUPVALUE portion need to change?

[DavidPles]

Big thanks for this tutorial! (I use it and it works right to my needs)
There are just small logic mistake - red marked sign has to be without "egual" - because old pricelist ends just day prior the new.

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]

    )

)

or much clear: ad -1 when calculate "Till Date column:

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)-1)

RETURN

_Result

[Anonymous]

Hi @vivran22 ,

How would this expression look like in power query m?

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
_Result

thanks!

[dax]

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.

[RemonKissen]

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