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")))
Solved! Go to 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
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
@WannaBePro I was thinking in python way. Glad you explained so nicely. Thank you so much 🙂
User | Count |
---|---|
256 | |
126 | |
104 | |
49 | |
49 |