Forum Discussion
Sliception, slicing a slicer
Hello all,
It is mostly all in the title.
I have a report that is destined to show data over any 14 days (chosen by the user), and on some graphs only I would like to show the data of the last 7 days of the past selection.
My data tables are all linked to the 'date' table used in the main slicer, which has one column with one row per date.
I have tried duplicating the date column, and using a slicer on duplicated column (and filtered by the 1st one), with relative filter on 'last 7 days' but it always will show the last actual 7 days and not the last 7 days of the period selected on the first slicer.
I have also tried creating a second table, only linked to the first one with the same slicing method strategy but to no avail.
I would like for users to only select a 14 day period, and not have to then select a second slicer and choose the last 7 days. It would be doable but not much user friendly.
Any idea on how to do so?
Here is one way to do it.
1. Make a disconnected 2nd Date table with something like
DisDate = DISTINCT('Date'[Date])2. Use that new Date column in your slicer3. Make a measure to be used as a visual-level filter for all your visuals (measures can't be used as a page filter) to limit the results to only the 14 prior to the selected dateLast14 = var seldisdate = SELECTEDVALUE(DisDate[Date])var thisdate = MIN('Date'[Date])return IF(thisdate<=seldisdate && thisdate>seldisdate-14, 1)4. Where you want to show only the last 7 in a visual, use a measure like thisSales Last 7 Only = var seldisdate = SELECTEDVALUE(DisDate[Date])return CALCULATE([Total Sales], KEEPFILTERS('Date'[Date]<=seldisdate && 'Date'[Date]>seldisdate-6))Pat
4 Replies
Here is one way to do it.
1. Make a disconnected 2nd Date table with something like
DisDate = DISTINCT('Date'[Date])2. Use that new Date column in your slicer3. Make a measure to be used as a visual-level filter for all your visuals (measures can't be used as a page filter) to limit the results to only the 14 prior to the selected dateLast14 = var seldisdate = SELECTEDVALUE(DisDate[Date])var thisdate = MIN('Date'[Date])return IF(thisdate<=seldisdate && thisdate>seldisdate-14, 1)4. Where you want to show only the last 7 in a visual, use a measure like thisSales Last 7 Only = var seldisdate = SELECTEDVALUE(DisDate[Date])return CALCULATE([Total Sales], KEEPFILTERS('Date'[Date]<=seldisdate && 'Date'[Date]>seldisdate-6))PatHello,
Turns out there are a few tweeks i need to implement. I feel I am close but not quite there yet.
I used you ideas and formula.
I created a distinct date table :
DisDate = DISTINCT('Date'[Date])
And am using a slicer based on it where the operator chooses a day.I then want to create a measure that associate a 1 for each date from the table that is
> Selected date -7 form the slicer
<= Selected date +7 from the slicer
And then another that associates a "1" only when the date form the tale is between the selected date on slicer and selected date of slicer -7.
This way i will have my two measure to use as filters on graph.
I used something like this :14perios = var seldisdate = SELECTEDVALUE(DisDate[Date])return IF(DisDate[Date]>seldisdate-7 && DisDate[Date]<=seldisdate+7, 1)
for the first one. But it does not work : Above : slicer where operator chooses date, below is the table that should show 14 dates with selected one in the middleasyou can see the table just takes into account the selected date on the slicer.
I guess my question is : how do you code the selected date in a slicer as an input parameter in a formula or measure?
Hello, easiest solution is to just use the filters with relative dates on them and a single 7day filter column.
1.Set the visual to be just the last 14 days:
2.make a column in the date table to act as a last 7 days or not filter, which might look something like:
Last7Days =VAR Today=TODAY()RETURNIF(AND('Date'[Date_]>=Today-7,'Date'[Date_]<=Today),"7Days","NO")Hello,
Thank you for the answer.
Unfortunately this does not suit my purpose, as my report is a 14 day report that is not necessarily about the past 14 days. (Data entries usually has 3 to 5 days of delay, so the report will show the past 14 days with data, meaning from d-4 or d-5 to d-18 or 19).
It is essential that the user chooses the 14 day period, which are not necesarily the last 14 days from today.
What i need from the second slicer is a relative date like you proposed, but where the starting point is not 'TODAY' but the last day of the previously selected 14 day period.
