Relative Date slicer: Last X Months drops first date

[jdbuchanan71]

When using the Relative Date slicer and selecting Last X Months the first date in the range is filtered out.

Version: 2.85.681.0 64-bit (September 2020)

Steps to recreate

1. Open a new PowerBI file

2. Create a date table

3. Modeling > New Table

4. Dates = CALENDAR( 43466, 44196 )

5. Add a measure to the model

6. Min Date = MIN ( Dates[Date] )

7. Add a slicer to the report with the date field

8. Set the slicer to 'Relative Date'

9. Select Last 1 Months

10. Add a card to the report

11. Add the [Min Date] measure to the card.

You will see that the [Min Date] is 1 day later than the date range, 9/1/2020 instead of 8/31/2020.

If you switch to Last 1 Week or Last 1 Year the low end of the date range and the measure match.

I also posted this on the main PowerBI forum.  

https://community.powerbi.com/t5/Desktop/Relative-date-slicer-bug-Last-X-Month-ignoring-first-date/m... 

[jdbuchanan71]

Here is an example of what I am seeing.

[jdbuchanan71]

"Last 2" months gives you 7/31 - 9/30, but the 7/31 data is missing. With "Last 3" you get 7/1 - 9/30, and the 7/1 data is included.  Looks like if you're at the end of a 30-day month, the behavior appears when you go back to a 31-day month. 

Thank you to @dedelman_clng for pointing this out.

[jdbuchanan71]

It seems to be more a case of the diplayed filter (the one on the date slicer and in the filter tooltip) not matching the actual filter (the one applied to the model).

[jdbuchanan71]

Based on further testing is appears that the slicer date range display and the filter tool tip display are using a fomula like this to calculate the starting date.

DATEADD ( TODAY() , - [X months] , MONTH ) + 1

Here is a screen shot from a file calcualted on 9/30/2020:

And I was able to replicate the dates using the formula and comparing it to the last date of the month.

I have uploaded my sample file here for you to look at.

https://www.dropbox.com/s/50k4gm8ueo5qv9e/Relative%20Date%20Testing.pbix?dl=0

[v-chuncz-msft]

@jdbuchanan71 

 The issue is reported internally : CRI 207973772.

[v-chuncz-msft]

@jdbuchanan71 

We’ve identified the issue. As the fix involves lots of evaluation, coding and testing, there is no ETA at this time.

[QSTC]

Hello,

I've observed the same kind of issue with my date slicer and a relative filter.

Could you confirm it has the same origin ?

In my case, I've set the relative date to week/month/year lengths and it fails to correctly update my charts.

As shown above, when I have the default lower value on the slicer, it displays the entire range of available dates.

The only way to get the proper display is to set the starting date oustide of the relative filter range (here 31/12/2019) and then coming back to the lower limit of the range.

I've checked for all relative date options and the same problem appears every time.

Regards,

Quentin