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