Dates become text when making pivot table from PowerPivot

[emesgee]

Dates are formatted correctly (and right-aligned) within PowerPivot, but when I make a pivot table in Excel from PowerPivot data, Dates turn to text (left-aligned).

I have seen this problem reported many times, but I've seen no remedies. I don't know how this hasn't been resolved yet, considering how crucial it is.

I'm using Office 2010. Is this problem fixed in Office 2013?

 

[Jacob Barnett]

This is a well known issue for non American English versions of Excel (which I'm guessing you are on).

Chris's post explains the solution: UK/US Date Format Bug in PowerPivot and SSAS Tabular | Chris Webb's BI Blog

 

[miguel.escobar]

it could also happen that the column that you're using has not been defined as part of your dates table and more specifically it hasn't been defined as THE dates column.

 

[emesgee]

it could also happen that the column that you're using has not been defined as part of your dates table and more specifically it hasn't been defined as THE dates column.

Cool, I didn't know you had to define a Dates column. How do you do it? Thanks.

 

[miguel.escobar]

1. Open the powerpivot window
2. go to your dates table
3. go to the design tab now
4. check the MARK AS A DATE TABLE and click on it
5. a new window will pop-up and you'll need to define your DATES column within that dates table

once you do that, refresh the pivot tables and try dragging the dates columns to either rows or columns. Unfortunately, the left aligned thing will not change but..you can now filter using the Dates filter option and much more

 

[emesgee]

1. Open the powerpivot window
2. go to your dates table
3. go to the design tab now
4. check the MARK AS A DATE TABLE and click on it
5. a new window will pop-up and you'll need to define your DATES column within that dates table

once you do that, refresh the pivot tables and try dragging the dates columns to either rows or columns. Unfortunately, the left aligned thing will not change but..you can now filter using the Dates filter option and much more

thanks, didn't know about this... but I was shot down by another limitation: no duplicates.

 

[Jacob Barnett]

Rule number 1 of PowerPivot Club - always use a separate dates table that consists of unique contiguous dates.