Use date slicer to filter pivot for all dates prior to selected date

[MD610]

I was hoping to get some ideas from others about the most efficient way to accomplish this.

I have a flat pivot table with a Date field in the rows. The date is formatted as 7/31/2012.

I would like to have a slicer that simply contains all the unique month/year (7 - 2012) combinations in my data.

When you select a given month/year in the slicer, I would like the pivot to not just filter to records for that month/year but also all the prior month/year records up until the filter selection.

I think I am on the right path:

I have a separate single column table with the unique combinations for month/year, that will not be related to my fact table, but will only be used for the slicer.

I have added a column to my fact table that shows the same month/year data format as my single column slicer table.

I have a measure (SlicerSelection) in my slicer table that displays the currently selected slicer value. This works as expected.

Now I need a second measure that compares [SlicerSelection] to the month/year column in my fact table and somehow indicates if the value is less than or equal to my [SlicerSelection]. I have attempted this several ways but am always getting an error in the measure.

I would then add the second measure to my pivot, hide the measure's column and filter my pivot by its value.

Any ideas on how to make the second measure give me what I want?

Or, am I totally going in wrong direction? Is there a better way altogether to accomplish what I want?

Any ideas or advice would help. Thanks!

 

[pete234]

Ok I just wanted to see if you had considered using any of the custom intelligence features.
It has calculations for Previous and current.
I work with a custom fiscal date table and quarter or week numbers that I can select from a slicer if needed.
http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx
I put my table in between all my reports joined by date.
I also might be unclear as to the goal of the second calculation.
If you can choose periods or what you need, you could choose any up to current, as long as you had data for it, right?
You can also use Slicer.Cache in vba if you wanted to define a button to a slicer based on custom need.
Just some ideas for you.