Showing results for 
Search instead for 
Did you mean: 

Relative Date slicer: Last X Months drops first date

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. 

Status: New
Super User II

Here is an example of what I am seeing.



Super User II
"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.
Super User II

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


Super User II

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.




Community Support



 The issue is reported internally : CRI 207973772.

Community Support



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