Latest Enterprise DNA Initiatives

Slicer or Filter to only look backwards

I’m trying to figure out the best way to avoid having future ‘blank’ records show up on the arrival & productivity charts on the summary tab of the attached pbix.
I think it would be best to just have a set filter looking at dates <= today rather than using a slicer BUT for the life of me I can’t figure out how to use the filter pane to limit the data to this range.
Seems like this would be based on the filter image attached:

But ‘relative’ dates concern me as to create bad data downstream…

Is there a better way to create a filter or slicer that only looks at the data backwards as these data points are only populated after the work week has been completed.

WeeklyTest.pbix (199.7 KB)

Thank you in advance

  • gina

Hi @gkavesh. Absolutely … but before doing any time intelligence, you should mark your dates table as a date table. Once that is done, you can add an [Is After Today] column to your Dates table (something like IF(Dates[Date] > TODAY(), 1, 0 )), then add a report or page filter for [Is After Today] = 0.

If it was me, I’d use the excellent Enhanced Date Table from Enterprise DNA expert @Melissa, which already has such an [IsAfterToday] column, and which can be found at
Extended Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum



This is a super simple problem to solve, IF you switch your Date table over to @Melissa’s awesome Extended Date Table. If you are using that, there is a field named ISAFTERTODAY. Just drop that field in the filter pane at the page level, and set it equal to FALSE.

I actually did a video a while back on many of the powerful uses of this simple field.

I hope this is helpful.

– Brian

Thank you -
I’ve been using the code provided by Melissa et al but guess I missed the step about having to designate the table as a ‘date’ table which also explains some other head scratching things that have stumped me in the date calculation details.

Really appreciate the help as I’m still very much a beginner


1 Like