cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mia2022
Frequent Visitor

Filter by date and sum columns in SharePoint list (Sum, filter and if function)

Hello,

 

I am trying to filter by Start_Date, if the sum of the Duration_in_mins for the same Start_Date is 60 or more than 60 (minutes), the "Fully booked" message will pop up, but the formula shows errors, could you help please?

 

My SharePoint list is as below: Start_Date is date and time, Duration_in_mins is a number column

Mia2022_0-1644882068804.png

 

Formula used for the Visible of the "Fully Booked" label card is:

If(Sum(Filter('Test 3D schemes and action logs',Start_Date=DateValue1.SelectedDate),Duration_in_mins) >= 60, true, false)

 

And the error message is as below: Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "Sum" operation is not supported by this connector.

Mia2022_1-1644882434941.jpeg

 

When I add a new entry in power apps (with Start_Date 19 Jan 2022), the Fully booked label hasn't poped up, although the sum of the Duration_in_mins of this date is more than 60, as shown in the SharePoint list screenshot above.

Could anybody help to resolve this or other suggestions?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @Mia2022 ,

 

Please try below formula instead:

 

Not(
    With(
     {temp:Filter('Test 3D schemes and action logs',Start_Date=DateValue1.SelectedDate)},
     Sum(temp,Duration_in_mins)
    ) >= 60
)

 

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

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

View solution in original post

2 REPLIES 2
v-jefferni
Community Support
Community Support

Hi @Mia2022 ,

 

Please try below formula instead:

 

Not(
    With(
     {temp:Filter('Test 3D schemes and action logs',Start_Date=DateValue1.SelectedDate)},
     Sum(temp,Duration_in_mins)
    ) >= 60
)

 

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

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

Hi Jeffer Ni, Thank you so much! It works perfectly!

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 (1,951)