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

[Anonymous]

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

[Anonymous]

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]

@Anonymous 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.  

[Anonymous]

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 

[v-xida-msft]

Hi @Anonymous,

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:

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

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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

Did you ever resolve?