cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nithishguptak
Helper II
Helper II

Count rows in A Date range

Dear powerapps Community, 

 

So I'm trying to count the number of rows between a date range from a sharepoint column called Daturm. I have used this formula But Its counting only one date.  CountRows(Filter('Journal Sicherheitsrelevante Ereignisse'; Datum = DateValue("1.10.2021 - 31.12.2021")))

 

DAte_value.PNG

 

Dates.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
WannaBePro
Advocate I
Advocate I

Hi @nithishguptak ,

What data type is that column?  Is it dates stored as a date type, or dates stored as text type?

From first glance, your formula reads as follows - "Fetch rows where the date is blank".

 

This is because DateValue("1.10.2021 - 31.12.2021") resolves to blank.  DateValue() is used to convert a date in text type format into date type format.  "1.10.2021 - 31.12.2021" is a string that is not recognised as a date, and so the result is blank.  So I would guess it's counting one row that has a blank date.

 

If you want to filter Dates that are stored in a Date type column by values that fall between two dates you would have to use non-delegable operators - namely greater than > and less than <. 

> and < are not recognised by SharePoint as valid query filter operators - meaning it will ignore the query and just return the first n rows where n = your data row limit (default 500, max 2000) configured on you app settings.  Power Apps will then apply the query on the first n rows retrieved to provide your filter - which is not ideal if you have more than n rows in your SharePoint list.

 

That said, your query might look something like this;

 

 

CountRows(Filter('Journal Sicherheitsrelevante Ereignisse'; Datum > DateValue("10/01/2021") && Datum < DateValue("12/31/2021")))

 

Obviously your date format is specific to your machine/region, so use the one that works for you.  Just test the formula DateValue("10/01/2021") on a text label to make sure you're getting a result.

 

If your dates are stored as text as opposed to dates, then you'd have to approach it quite differently.

 

WBP

  

View solution in original post

2 REPLIES 2
WannaBePro
Advocate I
Advocate I

Hi @nithishguptak ,

What data type is that column?  Is it dates stored as a date type, or dates stored as text type?

From first glance, your formula reads as follows - "Fetch rows where the date is blank".

 

This is because DateValue("1.10.2021 - 31.12.2021") resolves to blank.  DateValue() is used to convert a date in text type format into date type format.  "1.10.2021 - 31.12.2021" is a string that is not recognised as a date, and so the result is blank.  So I would guess it's counting one row that has a blank date.

 

If you want to filter Dates that are stored in a Date type column by values that fall between two dates you would have to use non-delegable operators - namely greater than > and less than <. 

> and < are not recognised by SharePoint as valid query filter operators - meaning it will ignore the query and just return the first n rows where n = your data row limit (default 500, max 2000) configured on you app settings.  Power Apps will then apply the query on the first n rows retrieved to provide your filter - which is not ideal if you have more than n rows in your SharePoint list.

 

That said, your query might look something like this;

 

 

CountRows(Filter('Journal Sicherheitsrelevante Ereignisse'; Datum > DateValue("10/01/2021") && Datum < DateValue("12/31/2021")))

 

Obviously your date format is specific to your machine/region, so use the one that works for you.  Just test the formula DateValue("10/01/2021") on a text label to make sure you're getting a result.

 

If your dates are stored as text as opposed to dates, then you'd have to approach it quite differently.

 

WBP

  

nithishguptak
Helper II
Helper II

@WannaBePro I was thinking in python way. Glad you explained so nicely. Thank you so much 🙂 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,871)