Lookup Value in another table based on multiple conditions and <= date

[rachaelwalker]

I want to analyze our purchase orders and check it against the inventory audit log to analyze the inventory levels for that date.  I have attached my file with sample data and details. The main issue is the audit table only records changes so when using a calendar table, it is not aligning with all audit dates so I need to find the qty of the previous date recorded for that product and warehouse. 

https://drive.google.com/file/d/1mhFoVVxsnHDGDhF2ebT5FVoU9P_EkPN0/view?usp=sharing

Purchase Orders

Date

Project

PO Number

Product ID

Warehouse

Qty Purchased

Inventory Audit

Date 

Product ID

Warehouse

Qty Changed

Qty On Hand

Calendar Table

Date

Expected Results

[v-nuoc-msft · Accepted solution]

Hi @rachaelwalker 

For your question, here is the method I provided:

Create measures.

Project Number = 
CALCULATE(
    MAX('Purchase Orders'[Project Number]), 
    FILTER(
        ALL('Purchase Orders'), 
        'Purchase Orders'[Date Purchased] = MAX('Purchase Orders'[Date Purchased])
    )
)

Qty On Hand = 
var _date = SELECTEDVALUE('Purchase Orders'[Date Purchased])
var _maxdate = 
    CALCULATE(
        MAX('Inventory Audit GROUPED'[Date]), 
        FILTER(
            ALL('Inventory Audit GROUPED'), 
            'Inventory Audit GROUPED'[Date] <= _date 
            && 
            'Inventory Audit GROUPED'[Warehouse] = SELECTEDVALUE('Purchase Orders'[Warehouse])
        )
    )
RETURN 
CALCULATE(
    SELECTEDVALUE('Inventory Audit GROUPED'[Qty On Hand]), 
    FILTER(
        ALL('Inventory Audit GROUPED'), 
        'Inventory Audit GROUPED'[Date] = _maxdate
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rachaelwalker]

Thank you! This worked perfectly. The performance is very slow but it works!