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.

[Pstork1]

There is a common problem when trying to use Equals as a Date comparison.  The problem is that even a DataOnly field stored in SharePoint is still stored as a DateTime field.  There is always a time component.  So when you compare the SelectedDate in a date picker to created you are actually comparing the selected date (at midnight) to the date and time when the file/item was created. Its not going to match.  

What you have to do is reformat the DateTime to be only a Date String on both sides of the equation. Then you will be able to use the equals. Try something like this

CountIf(DataSource,Text(Created,DateTimeFormat.ShortDate)=Text(DatePicker1.SelectedDate,ShortDate))

[mdevaney]

@Oskarkuus 

This problem is occuring because you are comparing a Date (DatePicker1.SelectedDate) to a DateTime(Created).  Use this code instead.

CountIf(Kop_Lista;Year(DatePicker1.SelectedDate)=Year(Created),Month(DatePicker1.SelectedDate)=Month(Created),Day(DatePicker1.SelectedDate)=Day(Created))

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

[Oskarkuus]

ok, i got this to work with both answers.

But something strange happens.

I get an result when selecting a date that is one week back in time.

But if i select a date that is yeasterday or any date in the last week... then i get 0

But i can open the the datasource and se several entries that have the date of this friday for example: 2019-09-27

[Oskarkuus]

Some testing:

If i add a gallery to my powerapp and set this filter:

Filter(Kop_Lista;Text(Created;DateTimeFormat.ShortDate) = "2019-09-27")

Then i get 0 results

If i set this filter:

Filter(Kop_Lista;Text(Created;DateTimeFormat.ShortDate) = "2019-09-20")

Then i get many results

But if i go to my datasource (sharepoint list) and sort the list from latest entry i see in the column Created entries from 2019-09-27

Why are they not appearing?

And then i test to create a new gallery and just sort it by column

SortByColumns(Kop_Lista;"Created";Descending)

Then i do see entries from 2019-09-27

Why is this?

[mdevaney]

@Oskarkuus 

I've tested the solution of @Pstork1 and my own solution.  They both appear to work on my side for dates during the past week and earlier.

Can you please unhide the Created column in your Sharepoint list and verify the dates in that column are as you expected?

[Oskarkuus]

a damit, i missclicked, it is not solved.

I have unhided the list column Created and the data in that column show over hundred of rows with the date 2019-09-27

But non of them show up in my gallery.

I have total of 3600 rows in my list.

The powerpoint app settings 2000 rows set, is this the problem?

i have indexed all my column in the list (5 columns) and requested a recreawl of my list.

is this the issue?

I have sorted my sharepoint list main view by date so i see the latest entries at the top.

[mdevaney]

Nothing is showing because CountIf is a non-delegable function. After 2000 rows it just quits.

Do either of these formulas return the correct COUNT of records when placed in a label? I am not at a computer right now.

—-
CountA(Filter(Kop_Lista,Text(Created,DateTimeFormat.ShortDate)=Text(DatePicker1.SelectedDate,ShortDate)))
—-
CountA(Filter(Kop_Lista,Year(DatePicker1.SelectedDate)=Year(Created),Month(DatePicker1.SelectedDate)=Month(Created),Day(DatePicker1.SelectedDate)=Day(Created))
—-

To see the results in a gallery simply remove the COUNTA function and place in the ITEMS property.

[Oskarkuus]

ok, can i somehow count the values even if i work with larger data-sets?

I expect this list to grow with a few hundred each week.

Using this formula on a gallery does not work for me:

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

Get error that CountA should be a singel-column table?

Using this formula on a label gives me the answer 2000

CountA(Kop_Lista.ID)

So i guess the issue is that my datasource is to large.

edit:

this formula in a gallery gives 0 reults in the gallery

Filter(Kop_Lista;Year(DatePicker1.SelectedDate)=Year(Created);Month(DatePicker1.SelectedDate)=Month(Created);Day(DatePicker1.SelectedDate)=Day(Created))

[mdevaney]

@Oskarkuus 
Delegation is tricky but you can still accomplish your goal.  Put this code in the Items property of your gallery

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

To get the COUNT put this in the Text property of a label

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

I fully tested the code of my side and did not get any delegation warnings.

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."