Date Range Slicer Ignoring Second Date.

[MM365]

I noticed that when using the slicer visual with a date column and setting it to between, the second date gets calculated as is before instead of is on or before.

I have a regular model (fact, dimension, and date table).
The data is up to date.
The relationships are okay.
I use the date column from the date table in the slicer.
I needed to filter the slicer to only let you select dates up until today so I used a day offset column and filtered the slicer to day offset is less than 1. (day offset is a column that has a 0 for today, a 1 for tomorrow, and a -1 for yesterday, ..., ...)

Lets say I select 1/1/2022 and 1/10/2022 in the slicer (today is 7/6/2022), when you use the filter icon on any of the other visuals on the page you'll see is on or after Saturday, January 01, 2022 and is before Tuesday, January 11, 2022, but if I select 7/6/2022 as the second date you'll only see is on or after Saturday, January 01, 2022 and the report only gets filtered to on or after 1/1/2022 but it goes till the end of my date table.
The reason that this happens is because Power BI tries to write the second date filter as and is before Thursday, July 07, 2022 but my day offset filter on the slicer visual doesn't allow selecting 7/7/2022.

@ power BI Team please update the calculation for the second date in the slicer to be calculated as on or before. Thanks.

[v-yangliu-msft]

Hi  @MM365 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _today=TODAY()
var _min=MINX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
MAX('Table'[Date]) >=_min &&MAX('Table'[Date])<=_today,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MM365]

Hi @v-yangliu-msft,
Thanks for trying to answer my question but unfortunately you just duplicated my problem.

I'll explain.
My problem wasn't that I can't select today's date in the slicer, my problem was that when I did select today's date in the slicer it didn't filter the report to only include dates until today.

If you check your report you'll see that when you select today's date the table visual doesn't stop at today's date.

There are three ways you can check this.
Scroll on the table visual and see dates past today.
Hover over the filter icon on the table visual and it won't say that the table is being filtered to only include dates until today.
Copy the query using the performance analyzer and then open it in dax studio and you won't see a filter to only show dates until today.

Thanks again!