cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
D_Whitfield
Level: Powered On

Filter gallery by comparing date time field with last 30 days criteria

I want to Filter a gallery datasource for the last 30 days of created items.


Seems simple enough and working with sharepoint lists before i have done it as it has been text fields that i can convert to date values and compare to filter out.

 

This connection is SQL the createdon field shows as e.g. 6/5/2014 1:47 PM

 

Showing the last 30 days is fine with the DATEADD function - DateAdd(now(),-30,Days)

 

Both date values appear the same format 6/5/2014 1:47 PM compared to 3/25/2018 4:47 PM.

 

This doesn't show any results so all i can think is they are not really the same format. 

 

Anyone ran into this issue?  I thought about converting to just date values both fields like i would do in SQL however they are datetime in the SQL backend so unlike text fields seems difficult.

 

Any advice would be great, maybe i am missing something because this seems a very simple filter to want to see in a gallery.

 

Comparing 30 days ago to a datetime field value

7 REPLIES 7
D_Whitfield
Level: Powered On

Re: Filter gallery by comparing date time field with last 30 days criteria

I also tried the trick of using that createdon field to a datepicker but this proved the same result.

 

Logic seems correct because i can do an IF statement to see which side the results are falling but to filter the gallery i just get no results

tianaranjo
Level 8

Re: Filter gallery by comparing date time field with last 30 days criteria

@D_Whitfield I have done something similar with the following:

ClearCollect(colFilterCurrentYear, Filter(colAllEvents, Value(DateYear) = Value(CurrentYear)
|| Value(DateYear) >= Value(CurrentYear) - 3))

 

I created a collection for items submitted in this year.  Then I filtered my 'master' collection based on CurrentYear - DateYear (DateYear is column in my SP list that pulls Year from the date the item was created.  

 

Hope that helps get you started.  

D_Whitfield
Level: Powered On

Re: Filter gallery by comparing date time field with last 30 days criteria

Thanks for the response i guess i'm seeing if anyone has done date add criteria for gallery items.

 

 

I can bring both the comparing dates 

 

1. SQL field createdon 

2. DateAdd(Now(),-30,Days)

 

I can pull them both to a label and they show the same format but this seems to not be the case when i make the gallery items e.g. 

where Createdon >=DateAdd(Now(),-30,Days)

 

get no items i can only think that it is comparing two different formats but shows them the same when brought out to a label.

 

Wondered if anyone has filtered gallery by DateAdd(Now()... function applied to an sql source 

Community Support Team
Community Support Team

Re: Filter gallery by comparing date time field with last 30 days criteria

Hi @D_Whitfield,

 

Could you please share a screenshot of your PowerApps app's configuration?

Further, could you please show more details about your SQL table?

 

I have made a test on my side and don't have the issue that you mentioned. My PowerApps app's configuration as below:26.JPG

The Items property of the Gallery control set to following formula:

Filter('[dbo].[TaskLists]',CreatedOn>=DateAdd(Now(),-30,Days))

Note: The CreatedOn column is a datetime type column in my SQL table.

 

Please check if there are some records existed in your SQL table. In addition, please refresh your SQL table datasource within your PowerApps app and then try your formula again to check if the issue is solved.

 

Please also take a try to re-create your PowerApps app to check if the issue is solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
D_Whitfield
Level: Powered On

Re: Filter gallery by comparing date time field with last 30 days criteria

Thanks for the reply have tried this and it works less than equal to but not greater than / equal to.

 

This is a CRM sql table i aim using (e.g. Accounts created) in the last 30 days.

 

Can't think what can be causing this as it works less than today e.g.

Filter('[dbo].[Account]',CreatedOn<=DateAdd(Now(),30,Days))

 

There is data for these criterias through checking straight SQL, can't think it wouldn't work with an SQL table from crm .

 

not sure what else to try 

D_Whitfield
Level: Powered On

Re: Filter gallery by comparing date time field with last 30 days criteria

just to add i am getting results for less than 30 days and get results but these results seem wrong e.g. Created on is a lot less than 30 days ago.

 

It must be now() or today() function is giving different data type or not understanding the sql from the CRM database, seems odd.

 

I don't care about the time would be good to convert both sides to just date format

SylvieLet17
Level 8

Re: Filter gallery by comparing date time field with last 30 days criteria

Having the same issue, where the formula works for Less than or equal to, but not greater than.

Did you ever resolve?

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 55 members 4,882 guests
Please welcome our newest community members: