How to use Date Picker as a Slicer to restrict what data to retrieved in PivotTable

[jgwhitfield2]

I am not sure if this is one or two issues. I am pulling data from my TSQL DB into a PowerPivot table and the field is formatted as such: CONVERT(char(10), IVH_DELIVERYDATE, 101). The data type in the PowerPivot table is also set to Date. When I create the pivot table from the PowerPivot dataset, the date data is being presented as the "General" format. When I attempt to change it via the "Format Cell's" function in Excel, I get no change in the data format.

I am not sure if this is causing my inability to select date range based on "Year, Month, and Day or not. Ideally, I'd like the PivotTable (PowerPivot) user to use the Date Picker to just select the dates that they desire the information for. If the DatePicker is not able to be used, I am happy to settle for the DateFilter function, but right now the only thing I am getting is a listing of EVERY SINGLE date in the DataSet.

 

[Jacob Barnett]

Don't know where you are based but this is an issue that some of us based in Australia have always had in V2. The problem seems to be that it gets confused with the format of the date in terms of the order of days and months and takes the easy way out by calling it text.

What works for me is if I pick a custom format from the very long list that is totally unambiguous such as '14-Mar-01'.

Let me know if this helps.
Jacob

 

[miguel.escobar]

drop the dates to the rows and you'll notice that you can do the DATES FILTER....Besides from this, try creating a hierachy

 

[jgwhitfield2]

Thank you for the replies.

Jacob, I changed the data format on the PowerPivot Table, but when I add a pivot table and pull the date field in it is still formatting as General (i.e. 3/5/2013)

Miguel, I am adding the date field to the "Row Labels" for the pivot table.


This is quite frustrating. I was using a work around by creating "year", "month", and "day" columns and breaking the date data into those columns. Using slicers with those columns work great, but the trouble comes when the data being analyzed is "mid-month" to "mid-month", (i.e. 18Feb2013 to 4Mar2013). In this case we are doing the one month, copying the data out into another sheet, doing the ending month and copying it's data into the sheet and then inserting a new pivot table on that data. this provides the data, but seems to quite the inefficient work around.

Any other ideas, I'm open for suggestions...Thank you!

 

[Jacob Barnett]

Here is a link to a screenshot of the custom date format I'm using and getting the date pulling through correctly etc. as well as access to the date filters etc.......

https://docs.google.com/file/d/0Bz5yMU2oooW2Zm0zcmgyN2VSQXM/edit?usp=sharing

Are you not able to replicate this behaviour? The custom format on the date is crucial in my experience.

Jacob

 

[miguel.escobar]

you need to make sure that within your dates table, you are defining the correct columns as DATES (using the powerpivot window) otherwise this won't happen

 

[jgwhitfield2]

Jacob,

Thank you for the screen shot. If I could get my data to replicate what you sent that would be great, unfortunately when I add the date column to an Excel Pivot Table the PowerPivot Table Date formatting is getting lost. Here is a screen shot of what is happening:

https://docs.google.com/file/d/0B_grSMhsyyr6QjQwVjBRZU5DZzg/edit?usp=sharing

Thank you for your assistance!

 

[miguel.escobar]

question, is this your FACTS table that you assigned as your dates table with the SHIP_DATE as dates column?

 

[jgwhitfield2]

Miguel,

I am initially only using one date import table. I have not added a Date Table yet because the data table I am working with is not being joined to any other table and all of the date content is uniform and consistent. My understanding of Date tables is that they should be used when working with multiple tables with date data. Am I incorrect in this understanding?


James

 

[miguel.escobar]

Hey James,

In my personal opinion, I'd use a dates table almost in every case possible....the thing is that when you have a dates table, you can set that table as DATES TABLE using the
DESIGN TAB
then click where it says MARK AS DATES TABLE and then choose the column where your DATES are stored...once you do that, you can drag the field to the rows or columns and you'll notice that magic happens =D

Try it and let me know what happens, even if the dates table just has 1 column (dates) it can make a difference.

Best,