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

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 I
Helper I

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

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,043)