Forum Discussion

RemonKissen's avatar
RemonKissen
Regular Visitor
6 years ago
Solved

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/1123652#M513249

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

Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    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

8 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      RemonKissen
      Regular Visitor
      6 years ago

      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

       

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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's avatar
      RemonKissen
      Regular Visitor
      6 years ago

      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's avatar
        vivran22
        Community Champion
        6 years ago

        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