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



## jgwhitfield2 (Mar 5, 2013)

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 (Mar 5, 2013)

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 (Mar 5, 2013)

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 (Mar 5, 2013)

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 (Mar 5, 2013)

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 (Mar 6, 2013)

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 (Mar 6, 2013)

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 (Mar 6, 2013)

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


----------



## jgwhitfield2 (Mar 6, 2013)

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 (Mar 6, 2013)

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,


----------



## jgwhitfield2 (Mar 5, 2013)

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.


----------



## jgwhitfield2 (Mar 6, 2013)

ok, I'll give it a go. Once I add the dates table, then I do a join with my data table? Then when I add the "Date column into the pivot table in Excel I need to use the "Date" from the date table not the Data table?


----------



## miguel.escobar (Mar 6, 2013)

correct, join the new table to the existing table using the SHIP_DATE and your dates column on your new table. Then, when creating the pivot table, use the dates column from your new dates table and you'll see how magic takes place...
try taking a screenshot but sometimes you can't capture magic in a simple picture


----------



## jgwhitfield2 (Mar 6, 2013)

Miguel,

Much your assistance has been much appreciated. I am quite new to the PowerPivot world. I am having trouble marking the date table as the "Date Table". from what I read this option should be on the  Design Tab, but as this pic shows, I don't have anything that refers to "Date": https://docs.google.com/file/d/0B_grSMhsyyr6MF9fNU1za203dUk/edit?usp=sharing

My head is beginning to hurt :-\


----------



## Jacob Barnett (Mar 6, 2013)

Are you using PowerPivot v1? If so upgrade immediately!


----------



## jgwhitfield2 (Mar 7, 2013)

Version 10.50.4000.0 is what I am running. Is this v1?


----------



## miguel.escobar (Mar 7, 2013)

it is. Do you really need to use this version? I'd recommend upgrading if possible


----------



## Siraj.Samsudeen (Mar 7, 2013)

The current version number of v2 is 11.0.3129.0 - this was a problem I also had. Uninstall your current version and reinstall from http://www.microsoft.com/en-us/download/details.aspx?id=29074


----------



## jgwhitfield2 (Mar 7, 2013)

My desktop guy just sent me this screenshot: https://docs.google.com/file/d/0B_grSMhsyyr6dEgybmd6T1RPOGc/edit?usp=sharing

The one he is telling me that I have is v2 and the one listed as 11.0.3129.0 is v3, according to the Microsoft signature. So, you guys are saying that prior to v3 the "make date table" function wasn't available?

I really appreciate all of the input.


----------



## miguel.escobar (Mar 7, 2013)

PowerPivot version
Version number
SQL Server 2008 R2 PowerPivot - initial release (V1)
10.50.1600.1
SQL Server 2008 R2 PowerPivot - updated release (V1)
10.50.2500.0
SQL Server 2008 R2 PowerPivot - current release (V1)
10.50.4000.0
SQL Server 2012 PowerPivot (V2)
11.0.2100.60
and above
Office 2013 PowerPivot - consumer preview (V3 or 2013)
2011.110.2809.6


<tbody>

</tbody>


----------



## jgwhitfield2 (Mar 7, 2013)

Removed v1 and installed v2.

Made "Calendar" table the "Date Table" based on the "Date" Column

Created a relationship from Calendar table (on Date column) to Orders table (Order Complete date) - Order table has multiple orders per day so joining from Orders --> Calendar gave me a duplicate record error:                                         https://docs.google.com/file/d/0B_grSMhsyyr6c0dEbjdyNGZVNVE/edit?usp=sharing

Added Calendar.Date to a Flat Pivot Table and was able to access Date Filters

Attempted to add OrderData.Order# to the pivot but received an error (The command was canceled.":                 https://docs.google.com/file/d/0B_grSMhsyyr6ZElwRXJJTjRxMUk/edit?usp=sharing

This made me curious about the "join" so I added the joined column to the pivot table (OrderData.OrderCompleteDate). This returned every record from the OrderData table (300 distinct dates) for each day in the Calendar Table (1828 dates) 548,400 rows of date data:                            https://docs.google.com/file/d/0B_grSMhsyyr6dFh1ZXJYbDMtQzA/edit?usp=sharing


This makes me wonder if my I did my join incorrectly. heehee, we addressed one issue but now there is another. Feels like a Monday.


----------

