# Fill in blank cells of a Pivot Table Value field area

• ### Question

• Hi,

In this workbook (http://1drv.ms/1oHk0QV), a normal Pivot Table has been created on the "Pivot Table" worksheet.  I'd like the blank cells of the Pivot Table to be filled up with the preceding non blank value.  So to take an example of Product A, the following is what I want:

1. H5:N5 should have 1
2. P5:S5 should have 2
3. U5:W5 should have 2
4. Y5:BA5 should have 3

The same should be done for the others as well.

I have attempted something on the "PowerPivot" worksheet but I do not get the correct result.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Thursday, September 4, 2014 12:19 PM

• there you go:

YourMeasure:=CALCULATE([State value],
LASTNONBLANK(FILTER(ALL(Table2[Week]), Table2[Week] <= MAX(Table2[Week])), [State value])
)

it basically finds the last week with a valid [State Value] and shows it

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Proposed as answer by Friday, September 5, 2014 8:21 AM
• Marked as answer by Friday, September 5, 2014 8:38 AM
Friday, September 5, 2014 8:18 AM

### All replies

• there you go:

YourMeasure:=CALCULATE([State value],
LASTNONBLANK(FILTER(ALL(Table2[Week]), Table2[Week] <= MAX(Table2[Week])), [State value])
)

it basically finds the last week with a valid [State Value] and shows it

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Proposed as answer by Friday, September 5, 2014 8:21 AM
• Marked as answer by Friday, September 5, 2014 8:38 AM
Friday, September 5, 2014 8:18 AM
• Thank you.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Friday, September 5, 2014 8:38 AM