Count rows in A Date range

[nithishguptak]

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")))

[WannaBePro · Accepted solution]

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]

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