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
     
     
samdthompson's avatar
samdthompson
Memorable Member
5 years ago

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()
RETURN
IF(AND('Date'[Date_]>=Today-7,'Date'[Date_]<=Today),"7Days","NO")
 
 

 

 

  • Zalexatwork's avatar
    Zalexatwork
    Frequent Visitor
    5 years ago

    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.