Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hazenm
Advocate II
Advocate II

Data Table Views Not Filtering Correctly on DATE fields

This has been happening to me for the past month or so... I think it definitely coincided with one of the releases. 
I'm wondering, someone else must be seeing this same behavior: 

Open up any Power BI report that has a table that you have added that has Date columns
Go to the Data Table view within Power BI Desktop

Click on one of the Date fields (Date or Datetime) and select "Between" filter

Set the dates to anything, click okay. 
Now go back to the "Between" filter OR review the min/max of your dataset to compare vs. the filter you had set. 
You'll find that both in the saved "Between" filter settings, and the actual min/max of your dataset has changed from what you've set it to. 

 

For example, I have a Sales Order Line Level detail table that I use frequently, with around 2 million rows. It has an "Order Date" which is a "Date" only type column. I might want to review data in here from let's say 1/1/2022 through 1/31/2022, so I setup the between filter to show data for items within these dates. 
Now I'll notice that the filter actually got set to 1/2/2022 through 2/2/2022. 
To get around this, right now, I'm just setting it up so it's 3-4 days before and after what I need, exporting to excel, then using excel to chop off the extra days, but I'm spending a lot of extra energy here that I'd rather not be spending. 

Anyone else experiencing this? For me it's not just this report, it's all of my reports. Before I realized what was happening it caused me to spend a lot of time hunting down what I thought were data inaccuracies in my reporting until I realized the export was grabbing the wrong days. 

Thanks everyone!

3 REPLIES 3
Hazenm
Advocate II
Advocate II

@amitchandak - I'm not sure I follow.  This is not a power query issue - this is literally in the data table view section on the main left nav. 
So when I say filtering on dates, I'm not talking power query, I'm talking about in that data table

Hazenm_0-1658490879393.png

Here's an example. This table, in this view, I've filtered that "Date" field (which is not date/time but date only) to 7/1 - 7/15. As soon as I go back and check what dates are filtered I see this: 

Hazenm_1-1658490970867.png

 



Is no one else experiencing this issue? Even through more updates, I'm still dealing with this on all of my reports. 

amitchandak
Super User
Super User

@Hazenm , check if you date has timestamp and filter is only working on date part

 

have date column

new sales date= datevalue([sale date]) 

 

use that in filter , join with date table and try

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.