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

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

DavidPles's avatar
DavidPles
New Member
3 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