# start and end date range input parameters to powerpivot table - how?



## randyvol (Jan 4, 2013)

PowerPivot is awesome.  I've been wondering for a long time why Microsoft didn't move Excel and SQL Server closer together; it seems they're doing so now.  
However, there is still one thing I have not been able to find a way to do; I have many pivot reports on SSRS that can be rendered after the user enters a start and end date.

I have a series of fact and dimension tables on my SQL Server, among them is a Time Dimension table.  In SSRS I can write SQL code that will quickly 'filter' the contents of that dimension
tbl leaving only dates between the start and end dates the user inputs.  That drives the datekeys to feed data from a fact table (such as sales) to the pivot table in the SSRS report.

I've never been able to figure out how I could reproduce this functionality in Excel, was hoping there would be something in Powerpivot; but alas I do not see a way to do this.
If only I could see how to do this in powerpivot I could easily convince my company to make the move from SSRS to PowerPivot for excel.

Is there a way to do this?
(I considered slicers, but that would get really cluttered - a slicer to pick the year(s), a slicer to pick the months, a slicer to pick the days - and that wouldn't do it anyway.  Consider a start date of 12/1/2012 and an end date of 3/1/2013.  The slicers would be 2012/2013; 12,1,2,&3; and 1 through 31.  That wouldn't produce the desired results.)

Thanks in advance for any suggestions.
(Just a bit about my skills - I'm quite proficient in SQL, learning DAX, somewhat competent in basic MDX.  I'm not a VBA developer though.)


----------



## miguel.escobar (Jan 4, 2013)

You might want to check out the timeline filter in Excel 2013. 
[Office 2013] Excel Filters : Slicer and Timeline « Raphael On Microsoft BI

and with DAX you can do magic, also note that you can even use the "DATES FILTER" functionality that Excel has when you input the dates as a filter or part of the rows/columns.

If you have any examples that you want to share and would like to reproduce on Powerpivot then we could possibly work on it.

Hope the info above helped you somehow.

have a good weekend!
Miguel


----------

