However, I simply want to limit the date slicer so that it only offers dates up to and including today - not any future dates.
I have tried this a couple of different ways - See Page 2
1, Apply a relative date option to the slicer to return the last 500 days (including today)
2, Filter the visual using a calculated column ‘Today or Prior’ = Y
Each option changes the slicer so that it does indeed only offer dates up to the current date, however if I drag the bar to the right, to select this last and current date, my selected date returns 31/12/2020 rather than 15/08/2020 (today) and breaks the report:
Thank you so much, once again you are coming to my rescue
I had actually tried that, but the reason I didn’t stick with it was because on my actual report we also have a line chart plotting stock trend vs last year. With ‘Today Or Prior’ set to Y it only goes as far as today:
I have just tried fixing it by modifying my Stock History Units SPLY measure to ignore the filter on Today Or Prior by using ALL, however it still only displays up to today:
Stock History Units SPLY =
CALCULATE([Stock History Units],
SAMEPERIODLASTYEAR(Dates[Date]),
ALL(Dates[Today or Prior])
)
Your instincts on this are 100% correct, but page level filters can be a bear to override.
I’ve got a complex problem in the queue ahead of this one, but in the interim take a look at the following post. I think it provides a roadmap for what we’ll need to do here - basically rebuild the SAMEPERIODLASTYEAR function as a CALCULATE/FILTER construct with ALL in the table portion of the filter.
See if this helps get you where you need to go. If not, give a shout and I’ll be glad to get back to it after I finish the current problem.
@BrianJ - Thanks for the link, I’ll try to take a look. I’m also up against it, off on holidays very soon and should have turned the laptop off yesterday!
@Rajesh - Yes that looks good, can you share your solution?
Edit: Apologies - I didn’t see the attachment, I’ll take a look now.
This was the problem I was experiencing as well. Not sure that this solution works – it looks like it does when you’re at the max value of the slicer, but when you back off of that value just one day both lines get terminated by the slicer value:
That’s great to hear. I love when folks new to the forum (or anyone, for that matter) start jumping in and providing solutions, so I think we all want to encourage that as much as possible. Often, when we think of benefits of the forum, we focus on the value provided to the original poster asking the question, but there are also tremendous benefits that accrue to the readers and to the people generating solutions. I had to cut this slide for time reasons from a recent YouTube video I did on the eDNA Channel, but I think it captures this dynamic pretty well:
. Really clever (and in retrospect, head-slappingly simple) solution. Will definitely file this one away for future use.