cancel
Showing results for
Did you mean:
Employee

## Date filtering

Hi all,

Date filtering is hurting my brain.

I'm trying to show only events with an event date within the last two weeks and include events happening today.

I tried this, but it's only including the events from the last 14 todays but not including today's events.

Ideas? Thx!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Your problem is that your Date really includes a time as well.  So, when you are saying Event_x0020_Date<=Today() here is how it really turns out (let's assume today's date):

5/10/2019 00:00  <= 5/10/2019 00:00

All seems like it should work...right?

Well, enter the real problem, you need to adjust for timezone.  Chances are, your date for event date is really in the system as 5/10/2019 04:00 (or some other hour depending on your timezone).  Well, that will never meet the criteria for <=.

So, you could try a couple of things:

1) Test this theory - change your formula to this:

`ShowColumns(         Filter(Events,               Event_x0020_Date >= DateAdd(Today(),-14) &&               Event_x0020_Date <= DateAdd(Today(), 1439, Minutes)               ),    "City", "Title")`

This will add 24:59 minutes to Today's date...still today, but end of day.

`ShowColumns(       Filter(Events,              DateAdd(Event_x0020_Date, -TimeZoneOffset(Event_x0020_Date), Minutes) >= DateAdd(Today(),-14) &&              DateAdd(Event_x0020_Date, -TimeZoneOffset(Event_x0020_Date), Minutes) <=Today()             ),       "City", "Title")`

This will adjust the time zone of the dates to be correct.  Then 00:00 should be 00:00

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Super User

Your problem is that your Date really includes a time as well.  So, when you are saying Event_x0020_Date<=Today() here is how it really turns out (let's assume today's date):

5/10/2019 00:00  <= 5/10/2019 00:00

All seems like it should work...right?

Well, enter the real problem, you need to adjust for timezone.  Chances are, your date for event date is really in the system as 5/10/2019 04:00 (or some other hour depending on your timezone).  Well, that will never meet the criteria for <=.

So, you could try a couple of things:

1) Test this theory - change your formula to this:

`ShowColumns(         Filter(Events,               Event_x0020_Date >= DateAdd(Today(),-14) &&               Event_x0020_Date <= DateAdd(Today(), 1439, Minutes)               ),    "City", "Title")`

This will add 24:59 minutes to Today's date...still today, but end of day.

`ShowColumns(       Filter(Events,              DateAdd(Event_x0020_Date, -TimeZoneOffset(Event_x0020_Date), Minutes) >= DateAdd(Today(),-14) &&              DateAdd(Event_x0020_Date, -TimeZoneOffset(Event_x0020_Date), Minutes) <=Today()             ),       "City", "Title")`

This will adjust the time zone of the dates to be correct.  Then 00:00 should be 00:00

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Announcements

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA