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 i...
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!

    • DavidPles's avatar
      DavidPles
      New Member
      2 years ago

      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

       

    • Solvera's avatar
      Solvera
      Frequent Visitor
      2 years ago

      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?