Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RemonKissen
Regular Visitor

Lookup Price within Date Range

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 NumberCost PriceActivation 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 DateItem NumberSales 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

1 ACCEPTED SOLUTION

@RemonKissen 

 

You may try this:

 

Add an Index Column in the Cost Price table post sorting it on Item Number & Activation Date

 

1.JPG

 

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

 

2.JPG

 

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

 3.JPG

 

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

View solution in original post

8 REPLIES 8
vivran22
Community Champion
Community Champion

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:

Capture.JPG

 

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

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

@RemonKissen 

 

You may try this:

 

Add an Index Column in the Cost Price table post sorting it on Item Number & Activation Date

 

1.JPG

 

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

 

2.JPG

 

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

 3.JPG

 

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

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?

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
Not applicable

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
Community Support
Community Support

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 dateNew Column Added
ItemNumberCostPriceFrom dateTo date
100031,9818-2-20201-3-2020
100032,172-3-20208-3-2020
100031,169-3-202010-3-2020
100034,0011-3-2020Till next update…
200031,9818-2-20208-3-2020
200032,259-3-202010-3-2020
200032,5011-3-2020Till next update…
300031,1118-2-20201-3-2020
300031,152-3-20208-3-2020
300031,259-3-2020Till next update…

 

Fact Table Sales transactions

   New Column
Delivery dateItem NumberSalesQuantityCost Price
18-02-202010001031,98
19-02-202010001031,98
20-02-202010001031,98
09-03-202010001031,16
10-03-202010001031,16
18-02-202020001031,98
19-02-202020001031,98
11-03-202030001031,25
12-03-202030001031,25
13-03-202030001031,25

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors