Effective date lookup

[nileshp]

In powerpivot, I have a sales register table with item id. And item weight master with following columns
item id, new wgt, effective date.

Every time weight of item changes, new entry is made in this table with effective date. An item weight can change several times. There is no relationship between these tables as item id can repeat in both tables


I want to grab item weight from item weight master in sales register table considering sales invoice date for effective date.

Hope I am clear
Nilesh

 

[Useful]

I have created a dummy file with this problem
https://www.dropbox.com/s/hf3dt27kb3prsyl/eff date dummy.xlsx?dl=0

Hello again,

your solution is:

=CALCULATE(max(wgt[new wgt]),FILTER(wgt,wgt[modify date]=CALCULATE(max(wgt[modify date]),FILTER(wgt,wgt[item id]=sales[item id]&&wgt[modify date]<=sales[Inv dt]))&&wgt[item id]=sales[item id]&&wgt[modify date]<=sales[Inv dt])
)

 

[Ozeroth]

An alternative solution uploaded here:
https://www.dropbox.com/s/pfccth7f7ldh8h2/eff date dummy - Owen suggested measure.xlsx?dl=0

I added an Item table and related both Sales and wgt tables to Item, otherwise leaving model unchanged.

The calculated column then becomes:

=
CALCULATE (
    CALCULATE ( LASTNONBLANK ( wgt[new wgt], 0 ), LASTDATE ( wgt[modify date] ) ),
    'Calendar'[Date] <= EARLIER ( sales[Inv dt] )
)

or

=
CALCULATE (
    LASTNONBLANK ( wgt[new wgt], 0 ),
    CALCULATETABLE (
        LASTDATE ( wgt[modify date] ),
        'Calendar'[Date] <= EARLIEST ( sales[Inv dt] )
    ),
    ALL ( 'Calendar' )
)

LASTNONBLANK is just there to break ties (which would only happen if you had two different weights for the same item on the same effective date).

Owen

 

[nileshp]

Awesome. Thanks a lot.

Nilesh