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

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

Status: New
Comments
Super User II

Here is an example of what I am seeing.

jdbuchanan71_0-1601492341419.png

 

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

date_filters.png

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:

jdbuchanan71_0-1601576274946.png

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

jdbuchanan71_1-1601576333277.png

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

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

 

 

 

Community Support

@jdbuchanan71 

 

 The issue is reported internally : CRI 207973772.

Community Support

@jdbuchanan71 

 

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