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!