cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimboSey
Helper IV
Helper IV

Show number of 'Complete' items between date pickers

Hi, 

 

I have an App linking to a SP list, within the SP list is a date column, which logs date an item is completed. 

 

In the App I have two date pickers, one for Start Date, one for End Date, I'd like to show the number of items with a completed date between the two dates specified in the date pickers. 

 

Thanks

 

3 REPLIES 3
TheRobRush
Super User
Super User

One Example, which is subject to delegation warnings (so only use it if your sharepoint source is under the 500/2000 (depending on your settings) row threshold.)

 

On the start dates date picker, place in onchange

 

Set(startdateVar,
DateTimeValue(
    Concatenate(
    "11:59 PM ",
    Date(Year(DatePicker2.SelectedDate),Month(DatePicker2.SelectedDate),Day         (DatePicker2.SelectedDate))
)))

 

In the end dates,

 

Set(enddateVar,
DateTimeValue(
    Concatenate(
    "12:00 AM ",
    Date(Year(DatePicker3.SelectedDate),Month(DatePicker3.SelectedDate),Day         (DatePicker3.SelectedDate))
)))

 

We do this because by default datepicker is setting them both to 12am which will technically return wrong in between dates as it will include dates ON the start date.

 

Now if you have a smaller datasource under the threshold a simple label with the following will count the rows where DATE falls between selected dates

 

CountRows(
Filter(
                SalesEvalCurrent,
                DATE>startdateVar &&
                DATE<enddateVar))
)

 

If you are over threshold you will need to first collect the ones that match, because countrows is not delegable.

 

You can do that by placing the following on a button, or in both onchanges of datepickers, etc wherever you want really.

 

ClearCollect(MyDateRange,
Filter(
                SalesEvalCurrent,
                DATE>startdateVar &&
                DATE<enddateVar)))

 after which you simply have the label count that new local collection

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Thank you, but will this provide me the number of items from the date complete column?

 

Thank you

Whatever column you want. In the datasource I used, DATE is my column containing the date so just change it to what you use.

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

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 (2,081)