Date Fileds SHown as Values

[Greig]

Hi All,

I am getting data via OLAP cubes. A number of date parameters exist from a field (date registered), such as Date.Date or Date.Month. When I do a Pivot Table the results are in date format and I can filter as dates, like Yesterday, Last Week. When I use PowerPivot to retrieve the data and create a Pivot Table, the format of the fields are not dates but Values. Why are they not Dates when PowerPivot is used? Many thanks

 

[Joe4]

Why are they not Dates when PowerPivot is used?

Welcome to the Board!

I do not use PowerPivot myself, but I may be able to answer at least the "why" part of your question. In Excel and Access, dates are actually stored as numbers, specifically the number of days from 1/1/1900. To see this, just enter any date in any Excel cell, and change the format to General. Changing the format does not change the underlying values.

So, while you are seeing "values", that really is just the numeric format of that date. So I think it is just a formatting issue. Can't you re-format those values as dates?

 

[Greig]

Thanks Joe4 - as I understand it the format of the data is set at the OLAP cube level and they are date fields.When I build a PivotTable, there are dimensions and attributes to use which show them as date values - I can filter as dates. Changing the format under PowerPivot to Date does not change the filtering from Value to Date.
Greig

 

[Joe4]

I have not worked with OLAP cubes or PowerPivot before, but perhaps it is dropping the format of the data. The point that I was trying to make is that dates really are numbers, as Excel views them. How they appear is just a formatting option, but it does not change their actual underlying value.

 

[Greig]

Thanks again - I understand your point. I am following it up with the person who set up the cubes.

I have not worked with OLAP cubes or PowerPivot before, but perhaps it is dropping the format of the data. The point that I was trying to make is that dates really are numbers, as Excel views them. How they appear is just a formatting option, but it does not change their actual underlying value.

 

[powerpivotpro]

Check the data type for your date column in the PowerPivot window, make sure it's a date in there - it might be stored as text but you can change it to date if so.

Also, you might need a separate date table, and use the Mark as Date Table feature (again on the ribbon in the PP window).

 

[Greig]

Hi Rob - thank you for this advice. I will certainly try it.
Greig

Check the data type for your date column in the PowerPivot window, make sure it's a date in there - it might be stored as text but you can change it to date if so.

Also, you might need a separate date table, and use the Mark as Date Table feature (again on the ribbon in the PP window).

 

[Greig]

It looks like a data type issue at the source. Thanks for the assistance.

Hi Rob - thank you for this advice. I will certainly try it.
Greig