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]

Hello,

see here

 

[nileshp]

Thanks, but can't use in my scenario.

I should have elaborated the table. SalesReg with relevant columns look like this
InvNo, InvDate, ItemId
I567, 23-07-2016,555
I568, 23-07-2016,589

ItemHistory table with rows relevant to ItemId 555
ItemId , ModifyDate, New wgt
555, 01-04-2015, 2.345
555, 21-07-2015, 2.356
555, 09-04-2016, 2.655
555, 01-09-2016, 2.877

In this example I want to pick 2.655 as that was latest wgt change as on 23-07-2016
If I can use lookup here, please guide me.

 

[liwi2]

Following. I have exactly same scenario. Can't create relationship 'coz of dups and need to get result for latest date.

 

[Useful]

Hello again,

try

=CALCULATE(LASTNONBLANK(Table1[New wgt],"") ,FILTER(Table1,Table1[ModifyDate]<=[InvDate]),FILTER(Table1,Table1[ItemId]=[ItemId]))

 

[Useful]

Following. I have exactly same scenario. Can't create relationship 'coz of dups and need to get result for latest date.

for your case also

=CALCULATE(max(Table1[ModifyDate]) ,FILTER(Table1,Table1[ModifyDate]<=[InvDate]),FILTER(Table1,Table1[ItemId]=[ItemId]))

 

[liwi2]

Thank you for both options. It is definitely better solution then I had in mind. Seems I over complicated everything :/
Is there perhaps a way to lookup the weight which has modify date closest to invdate.
I tried with beetweendates but with little success.

 

[Useful]

Thank you for both options. It is definitely better solution then I had in mind. Seems I over complicated everything :/
Is there perhaps a way to lookup the weight which has modify date closest to invdate.
I tried with beetweendates but with little success.

my previous suggestion should work in your case. What is your aim using of DATESBETWEEN()? get sum, count or smth. of given period?

(P.S it'll better to use table names with column names. It'll work correctly)

 

[nileshp]

Hello again,

try

=CALCULATE(LASTNONBLANK(Table1[New wgt],"") ,FILTER(Table1,Table1[ModifyDate]<=[InvDate]),FILTER(Table1,Table1[ItemId]=[ItemId]))

Thanks again,

But this is returning the highest weight in the new wgt column without considering any filters. updated weight need not be higher than previous. I am trying to tweak your formula but so far no success

 

[nileshp]

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