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
EHQG
New Member

Filtering by date returns no results

Hi All,

 

I am having an issue with my date table.  When I filter the dates I cannot get "01/04/2022" to return same for "01/04/2022".

Those dates exist, but when I try to filter for dates that equal "01/04/2022" it does not return any results. When I filter by dates between "01/04/2022" and "24/04/2022", it will return everything but "01/04/2022". I can filter by the dates within the query editor.

 

The date exisits in the column

EHQG_3-1657188568054.png

Filtering for dates that equal 01/04/2022

EHQG_1-1657188476291.pngEHQG_2-1657188511801.png

Filtering on or after 01/04/2022 and on or before 24/04/2022

EHQG_4-1657188631157.png

EHQG_5-1657188660784.png

 

Any help is much appeciated.

Cheers

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@EHQG , Change datatype to datetime (Fact Table date column) and choose a format with time. Check if there is timestamp other than 00:00 or 12:00AM

 

if yes, then create a date column use that in join/filter, and try

 

Date 1 = Datevalue([date])

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

Hi @EHQG ,

 

Did you get your problem solved, and if so, please share your method. If not, as @amitchandak  said, it is the precision of the timestamp that is causing the mismatch. You can try converting them with format().

 

New date = Format('table'[date], "dd mmm yyyy")

 

Then change the column type to date and it maybe works.

 

 

Best Regards

Community Support Team _ chenwu zhu

 

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

 

 

 

amitchandak
Super User
Super User

@EHQG , Change datatype to datetime (Fact Table date column) and choose a format with time. Check if there is timestamp other than 00:00 or 12:00AM

 

if yes, then create a date column use that in join/filter, and try

 

Date 1 = Datevalue([date])

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.