Report to show only up to current date

Hi all,

Please see attached example: Stock Report Last 6 Months v1.pbix (119.6 KB)

My report is designed to show our stock position at End Of Month, for the last 6 months from my Stock History table.

I have a date slicer which allows the user to review the stock position on any historical date. This all works fine on Page 1.

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:

If I drag it back one day to be the 14/08/2020 all works ok?

Any help greatly appreciated as always!!



Try putting the filter on the page, rather than on the visual:

I tried that and was unable to break the report in the way you described.

  • Brian


Thank you so much, once again you are coming to my rescue :grin:

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:

Whereas we would like to keep the full year line for prior year:

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],
    ALL(Dates[Today or Prior])

See page 3 of v2 attached: Stock Report Last 6 Months v2.pbix (120.4 KB)

The use of ALL still stumps me. It seems to work in case when I using it against a table, but not when used to ignore a column?




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.


  • Brian
1 Like

Hi @Mark

Hope this is what you are expecting.

Stock Report Last 6 Months v2.pbix (118.2 KB)

1 Like

@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.


Hi @Rajesh

Your solution to apply ‘Today or Prior’ to both the Slicer and my bar chart works fine and gets me to where I want to be.

Many thanks again to both of you for your help here!



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:

  • Brian

Hi @BrianJ

Yes you are right. Here is the new solution for Slicer back values as well.

Created new Table Dates2 and New measure for PY values

Stock History Units SPLY new =
CALCULATE ( [Stock History Units], SAMEPERIODLASTYEAR ( Dates2[Date] ) )

First Image Slicer Max Value and Second Image Slicer back values.

Stock Report Last 6 Months v2.pbix (136.9 KB)



:+1:. Really clever (and in retrospect, head-slappingly simple) solution. Will definitely file this one away for future use.


  • Brian


Thanks Brain. You are really encouraging me giving likes and providing feedback for my solutions.

1 Like


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:

Anyway, keep up the great work, and look forward to seeing more of your solutions soon…

  • Brian