Slicers - getting data before the selected date

[serky]

Hello all

I need some help setting up slicers/measures correctly to manage the scenario where I want the report and other slicers to show information that is prior to &/or equal to a selected date ie - I do not want the standard slicer behavior where it gives returns data at a specific date.

I have developed an example below using stores and date open. The intent is that the dashboard and slicers will show all stores open at the selected point in time and the associated info eg - if the user chooses:
- 1/1/13, I only want stores 1 and 2 to be available in the slicer and pivots to show data for stores 1 and 2.
- 1/1/14, I want stores 1,2 and 3 to be available in the slicer and shows data for all 3 stores.

I'm not sure of the best way to tackle this. Should I just set up my measures to handle this? Do I need other tables? Can I use slicers in a smarter way? Or is there another solution?

Thanks



Example

Date Table
1/1/12
1/1/13
1/1/14
1/1/15

Stores Table
Store Open Date
1 1/1/13
2 1/1/13
3 1/1/14

- DateTable[Date] links to StoreTable[Open Date] via a relationship

 

[ImkeF]

- 1/1/14, I want stores 1,2 and 3 to be available in the slicer and shows data for all 3 stores.

Hello Serky,
I'm not sure if I understood your request right, but considering the above quoted I understand that you want to create a pivot table that is sliced/filtered by 1 date and showing values of >1 dates (and having a slicer that shows selected attributes of these >1 dates). So your slicer contents correspond to your measure contents - challenge is how to create that measure.

(The end of) your filter criteria determines the end of the timespan of your new measure - how are you going to determine the start?
Is this
a) the very first start of your facts table or
b) another value that you want to filter/specify as well?
c) is it always x-years/months before the selected end date?

If it's b) then I'd just copy the date table and connect it to your fact table - there you go with to date tables: one that specifies your StartDate=MAX(DateTable1[Datekey]) and the other one your EndDate=MAX(DateTable2[Datekey]). Both connected to the store open date.

Your new measure: NewSum:=CALCULATE(SUM(StoreTable...),FILTER('StoreTable',StoreTable[Store Open Date]>=[StartDate]),FILTER('StoreTable',StoreTable[Store Open Date]<=[EndDate]))

For the other cases:
a) no additional table needed, FILTER('StoreTable',StoreTable[Store Open Date]<=[DateFromDateTableFilter])
c) like a), add StartDateFilter by DATEADD, Period, -x (make sure that your DateTable has no gaps).

hth, Imke

 

[ImkeF]

Correction to my previous post: The date tables where you are taking your selection from (EndDate and maybe StartDate) need to be disconnected from your stores table and from the original date table.