I am trying to use PowerPivot to summarise sales data.  The data has 3 dates:

- Production date

- Wholesale date

- Retail date

My sales data stores dates in text format so my first step was to create columns for DATEVALUE in PowerPivot.  I then linked this to my Date Table which has all dates in column A and "Month", "Year" etc in following columns.

I want to be able to use a slicer on June 2012 for example and it will show me the number of items produced, wholesaled and retailed in that month.  To do this I have:

- Created a pivot table with region in rows and count of production, wholesale and retail in columns

- Insert a slicer linked to my Date Table rather than to individual date columns

Now when I use the slicer to filter I am getting unexpected results.  It appears that the slicer first filters on production in June, then on wholesale in June and then on retail in June meaning that the numbers are incorrect. If you imagine filtering this manually you would get incorrect results if you forgot to clear your filters every time you checked a new column.

I have searched for hours online and checked my date table is correct but no luck.  Help would be appreciated!

Thanks

Sam

Excel 2010, Tables, PowerPivot, PivotTables, Slicer
With regular and Power PT.
http://www.mediafire.com/download/15ly8x65utayao6/08_09_13.xlsx

I have looked at the linked file, but could not understand what is the issue. When sliced to the same dates, pivot tables from Pivot_reg and Pivot_Power produce the same results. Maybe you could describe with an example what result you would expect for a given combination of inputs. What you expect vs what you get.

BTW, I also noted that you did not mark your calendar table in Powerpivot was not marked as a date table.

The undocumented issues reside with Shenry87.
There are no issues if his guessed source data is (re)arranged as shown.

Thanks for this.  It seems like a logical way to turn my source data into three tables and stitch them together. I am now just looking for a macro which will do this and update automatically rather than manually past appending.

Appreciate your help.

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, better to post your question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.