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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,316)