Forum Discussion

Zalexatwork's avatar
Zalexatwork
Frequent Visitor
5 years ago
Solved

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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 slicer
     
    3. 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 date
     
    Last14 = 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 this
     
    Sales Last 7 Only = var seldisdate = SELECTEDVALUE(DisDate[Date])
    return CALCULATE([Total Sales], KEEPFILTERS('Date'[Date]<=seldisdate && 'Date'[Date]>seldisdate-6))
     

     

    Pat
     
     
mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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 slicer
 
3. 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 date
 
Last14 = 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 this
 
Sales Last 7 Only = var seldisdate = SELECTEDVALUE(DisDate[Date])
return CALCULATE([Total Sales], KEEPFILTERS('Date'[Date]<=seldisdate && 'Date'[Date]>seldisdate-6))
 

 

Pat
 
 
Zalexatwork's avatar
Zalexatwork
Frequent Visitor
4 years ago

Hello,

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?