PowerPivot Measure Formula

**Twi78** · 03-28-2012, 08:05 AM

I have the following powerpivot table, and I want to create a measure called Opening_Stock, that calculates the last 'event' (or date) prior to 01/02/2012. Anyone provide any ideas how to do this?

Event Part_Number Month Year Date Qty
10598 ADF345 1 2012 15/01/2012 5
10599 ADF345 1 2012 29/01/2012 4
10600 ADF345 2 2012 15/02/2012 6
10601 ADF345 2 2012 29/02/2012 7
10602 EFT689 1 2012 15/01/2012 5
10603 EFT689 2 2012 29/02/2012 3
10604 DBM165 1 2012 15/01/2012 4
10605 DBM165 1 2012 29/01/2012 5
10606 WTP726 1 2012 15/01/2012 7
10607 WTP726 1 2012 29/01/2012 9
10608 WTP726 2 2012 29/02/2012 8

Thanks

**dilipandey** · 03-28-2012, 08:45 AM

Hi Twi78,

Assuming you have this data in column A to F, put 01/02/2012 in H1 and use below formula in I1 :-

{=OFFSET($F$1,MATCH(MAX(IF($E$2:$E$12<=$H$1,$E$2:$E$12,"")),$E$2:$E$12,0),0)}

Above is an array formula and need to be confirmed using Ctrl Shift Enter

Regards,
DILIPandey

<click on below 'star' if this helps>

PowerPivot Measure Formula

LinkBack

Thread Tools

Rate This Thread

Display

PowerPivot Measure Formula

Re: PowerPivot Measure Formula

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions