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

Filter Data in Data table by Date or Year

Hi,

 

First of all, I want to say that I've seen another similar post but I don't want to "open" it again.

I have a drop down list with year text ("2015";"2016";"2017"; etc) and a data table with a StartDate Date column.

I'd like to filter this data by year only but I think that I have some error with format data...

My formula is 

SortByColumns(Filter('[dbo].[FORECASTSALES]'; DateValue(Text(STARTDATE; DateTimeFormat.ShortDate))>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

But a blue icon comes up on the left hand side corner with message: "Suggestion: Part of this Filter formula cannot be evaluated remotely due to service limitations..."

 

It shows some data (only 6 rows) but there are many in the database.

 

I only want filter by year but if I write something like this, the blue icon appear again...

SortByColumns(Filter('[dbo].[FORECASTSALES]'; Year(STARTDATE)=Value(ddYear.Selected.Value));"STARTDATE";Descending) 

Any help will be appreciate.

Thanks a lot!

1 ACCEPTED SOLUTION

Accepted Solutions
Sienna
Level 10

Re: Filter Data in Data table by Date or Year

Yes... You can't filted SQL databse with dates.. There is a workaround where you will have to save all your dates as Value and save them as longinteger in SQL and if you would want to filter for example year 2015 then you will have to filter it like this

 

Filter(DatabaseName, DateColumn>=Value(Date(2015,1,1)) && DateColumn<Value(Date(2016,1,1)))

 

It is a bit tricky but it works and it is reliable and you don't have to collect the database into a collection

View solution in original post

10 REPLIES 10
Sienna
Level 10

Re: Filter Data in Data table by Date or Year

I assume you are using sql table. If so You can't do it. You have to collect the table into a collection and filter that collection

vanels
Level: Powered On

Re: Filter Data in Data table by Date or Year

Oppss!!

Yes, it is a sql table.

And... it's posible to create a collection with all data table?

I supose that then, my data table will be connected with the collection but in what momment I create the collection?

 

In other test I've done, I've seen the format of STARTDATE field...

With this formula

SortByColumns(Filter('[dbo].[FORECASTSALES]'; STARTDATE>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

the message error is:

Invalid Mashup expression using splies values: operator < cannot be applied to DateTimeZone and DateTime...

 

 

Thanks

Sienna
Level 10

Re: Filter Data in Data table by Date or Year

To create a collection just type in your screen OnVisible property for example

 

ClearCollect(CollectionName, 'NameOfYourDatabase')

 

The collection will not be connected and you will have to update the collection everytime you make some changes with your SQL database. 

Sienna
Level 10

Re: Filter Data in Data table by Date or Year

Yes... You can't filted SQL databse with dates.. There is a workaround where you will have to save all your dates as Value and save them as longinteger in SQL and if you would want to filter for example year 2015 then you will have to filter it like this

 

Filter(DatabaseName, DateColumn>=Value(Date(2015,1,1)) && DateColumn<Value(Date(2016,1,1)))

 

It is a bit tricky but it works and it is reliable and you don't have to collect the database into a collection

View solution in original post

vanels
Level: Powered On

Re: Filter Data in Data table by Date or Year

Yes!! This is the last formula I used but i put above, It shows a error message about the format (datetimezone and datetime).

First if all, I could try to fix this formats.

Sienna
Level 10

Re: Filter Data in Data table by Date or Year

What formula are you talking about now? Collecting to collection or filtering?

vanels
Level: Powered On

Re: Filter Data in Data table by Date or Year

Hi,

 

I refer to this formula:

SortByColumns(Filter('[dbo].[FORECASTSALES]'; STARTDATE>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

 

Sienna
Level 10

Re: Filter Data in Data table by Date or Year

Hi @vanels

That formula will not work until you rework the database. If you insert a label into your app and change the Text property to 

Value(Date(ddYear.Selected.Value);1;1))

then you will get a long number. For example Value(Today()) is 1520899200000. That means if you want to do it this way, you have to rework the database. Mainly your column for date. You have to change the data format to longInteger. and re-save all dates to Value(Date) that means to that long number. There has been always some problems with dates in SQL so I prefer to do it this way. It is up to you what you gonna chose. There is also some good reading about it

 

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/

vanels
Level: Powered On

Re: Filter Data in Data table by Date or Year

Ok, ok... I saw the same formula as mine and I was happy :), but I didn't realize that it was needed to make this changes in the database.

 

Well, I understand the options.

Thank you very much for your patience.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,466)