Displaying all dates in PowerPivot table

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I'm having a really hard time with the fact that there is no way to show all dates in a PowerPivot table when they have no data. The common solution I find online is to create a measure returning 0 in place of blanks, but this doesn't work for me because my PivotTable row layout is Years > Months > Customer ... and I would want customers to be filtered out that are blank (returning 0's causes every customer to always be returned)... but I do want every month regardless of blanks.

Is there any way to detect that if I am at the month level (or any specific row level for that matter)?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I'm not clear what the issue is. But there is a setting in popivot table options to hide rows with blank values. You can turn this off, and the pivot will display all items on rows (e.g. Date) regardless if here is any data to show.
 
Upvote 0
I'm not clear what the issue is. But there is a setting in popivot table options to hide rows with blank values. You can turn this off, and the pivot will display all items on rows (e.g. Date) regardless if here is any data to show.

Thanks. The issue though is that that setting applies to ALL row items. So if my row field list was,

Dates
Customer
Product

Then I only want to show all items (with no data) on Dates, but not Customer and Product. Basically, I want to see a consistent 12 months regardless of filtering, but all other hiding of blanks to occur.

In standard pivots, you can set the Show Items with Missing Data at the field level, but PowerPivot only allows it to be applied across either all rows or all columns.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top