CountIf - not working to count "created" date with datepicker?

[Oskarkuus]

I need some help to solve this issue.

I have two datasources, sharepoint lists:

Kop_Lista

Kassa_Lista

In the sharepoint list Kop_Lista

I want to count how many rows have been created at a specific date, the date that the user picks from a datepicker control in Powerapps.

But i cant get that to work by doing this:

CountIf(Kop_Lista;DatePicker1.SelectedDate=Created)

I put above code in Text control in a Label.

I just get 0 as the result even tho i can see i have items created on the date that is selected in the datepicker.

I have also tried this:

CountIf(Kop_Lista;Text(DatePicker1.SelectedDate;DateTimeFormat.ShortDate)=Text(Created;DateTimeFormat.ShortDate))

I dont get any errors. But the result is just 0

My goal after getting the date to work to then add another criteria to the IF statement.

But i need the date to start working.

[Oskarkuus]

Using this code in a label text function

CountA(Filter(ShowColumns(Kop_Lista;"Created");Created>=DatePicker1.SelectedDate;Created<DateAdd(DatePicker1.SelectedDate;1;Days)))

I get no errors, but the result is 0

Datepicker is set to 2019-09-27

And my datasource have over 100 entries with that date.

Putting this code in a gallery
Filter(ShowColumns(Kop_Lista;"Created");Created>=DatePicker1.SelectedDate;Created<DateAdd(DatePicker1.SelectedDate;1;Days))

The gallery is empty

The gallery have one label with this code: ThisItem.Created

So im not sure, it does not seem to work on my side for some reason :S

[Oskarkuus]

I might have found a solution.

I setup a hidden gallery with this code

ShowColumns(SortByColumns(Kop_Lista;"Created";Descending);"Created")

Then i setup this label

CountIf(Gallery6_1.AllItems;Text(DatePicker1.SelectedDate;DateTimeFormat.ShortDate)=Subtitle3_1.Text)

Now it seems i get the correct result back.

But it is very hard to check this :S

EDIT:

Nevermind, i am not getting data for all dates, so i cant count older data with this and im not sure if this only works because data have now been loaded once .

[RezaDorrani]

Hi @Oskarkuus 

Firstly - Counting functions such as CountRows, CountA, and Count can't be delegated. (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview)

Second - Filter function on datetime fields has limiations with respect to delegation 

Null assignment is delegable for a DateTime and Date field. Actual DateTime and Date values are not delegable. Filtering a DateTime or Date field for Blank is supported. Filter on not null is not supported. Only the = (not the <>) operator is delegated to SharePoint (the Not operator is not delegated.) (https://docs.microsoft.com/en-us/connectors/sharepointonline/)

To work with Datefields and with delegation (working with large data sets) - you will need to store the dates in a numeric type column in order to filter on date fields - Check this below video on how this works

https://www.youtube.com/watch?v=eCMuXPI1Qok

Finally, to get the count of the number of items

Set(counter, CountRows(sortbycolumns(filter(.....),"DateNumberField"))) - this will only give you max 2k (assuming 2k is the delegation limit) 

So you would have to write logic to fetch the next batch of data and so on

Another option would be to call MS Flow from PowerApps to get the respective count of the query 

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mdevaney]

@Oskarkuus 

If this formula that I supplied earlier doesn't return the results you wanted to see in the gallery, I'd tend to think the problem is with the data rather than the app coding.

Filter(NamesList,Created>=DatePicker1.SelectedDate,Created<DateAdd(DatePicker1.SelectedDate,1,Days))

Posting screenshots of your data with the Created column beside it is the next step here.

[Oskarkuus]

here are some pictures.

your code gives me an empty gallery sadly.

cant see any errors.

[mdevaney]

I see a warning symbol on your gallery. What does the warning say?

Also your formula is using semi-colons where commas should be. Can those be changed to commas or is it a Sweeden format?

What a tough issue to resolve! I really thought we had it there.

[Oskarkuus]

It is a delagation warning on Created.

See picture

[Oskarkuus]

It seems to be very hard to count things that are larger than 2000

im now thinking that i should not do calculations in powerapps atall...

but then where could i  do them. would i be able to use power bi and create reports.

or could i use flow and genereate things...

[RezaDorrani · Accepted solution]

@Oskarkuus 

It is because of the delegation limitations with respect to date fields and count functions

Flow or Power BI report are your best bet

Power BI will require licensing for all users using the report (Pro license)

Flow - call flow from PA - pass the date - query data - generate report and email the user

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mdevaney]

@RezaDorrani
Wow, I did not know that Date fields are not delegable in SharePoint. Hopefully that will change in the coming days ahead.