Pivot with Dates as Row Labels

[jason4444]

Hi all,

I am creating a pivot table with dates as the row labels. If there is no data that corresponds to a certain date, the pivot table skips that date. For example, I might see a pivot that looks like this:

Date Random Value
1/1/16 5
2/1/16 7
3/1/16 6
4/1/16 3
6/1/16 9
7/1/16 2
8/1/16 1

*Not sure if this is relevant but the Date column is a formula: =Date(Year(A2),Month(A2),1)

In this example, the pivot doesn't show the date of 5/1/16. Is there a way I can have the pivot table show all the dates, even if there isn't a value that belongs to that date?

[CK76]

You need "Date List" table to create data relationship on Date Column.

Set up like image below.
Relation.JPG

Build PivotTable from data model, using DateList as row label. and Val from original table.

Then, go to PivotTable options, "Display" tab. There, find "Show items with no data on rows" and check.

See attached for sample.