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

Sum and filter collection

Hello

 

I am trying to sum a column in a collection based on the user and the month for a sales team.

 

The collection is colTotalSale and the column to be summed is TotalSale.  

 

Sum(Filter(colTotalSale, Date=Month(Now()), User=User().FullName), TotalSale, thought this would work but sadly not.  Probably spent too much time on it and cant work out what the error is.  

 

Could some fresh eyes point out the obvious to me 🙂

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
SilvermarD
Frequent Visitor

Fantastic, no errors with the formula.  Unfortunately, its not producing a result, so something is a miss somewhere.

 

Many thanks

View solution in original post

7 REPLIES 7
Adrian_Celis
Super User
Super User

Hi @SilvermarD ,

If you are going to filter by month then you have to use a range. Something like:

 

Filter(colTotalSale, Date >= Date(Year(Now()),Month(Now()),1), Date <= DateAdd(Date(Year(Now()),Month(Now()),1),1,"Months"), User=User().FullName)

 

 

Thanks Adrian.

 

This is throwing up an error, saying the property on this control expects text values.  Tried to make the date range work but cant seem to get it right.

 

 

 

Hi @SilvermarD 

Can you show me the formula that you've done so far and what is the error saying exactly?

Hello Adrian

 

Thanks for getting back to me.

 

I tried the filter you sent me but adding in Sale total as this is the information I am trying to extract.  The error was Unexpected characters, formula contains 'ParenClose' where Eof is expected.  Also, invalid argument type (table).  Expecting a number value instead.

 

Had a look at how to do date ranges and came up with below, however the error with this one is invalid argument type (table).  Expecting a number value instead.

 

Sum(Filter(colTotalSale, DateDiff("1/1/22", Now()) < 0 && DateDiff(Now(), "31/1/22") > 0), User, Sale_Total)

 

Thanks

 

 

Hi @SilvermarD 

 

If the error is 'ParentClose' then most likely it's missing a parentesis. So what is the 'User' in your formula? Is it filtering out the user? If so then it should be something like:

 

 

 

Sum(Filter(colTotalSale, DateDiff("1/1/22", Now()) < 0 && DateDiff(Now(), "31/1/22") > 0, User = "Some Name"), Sale_Total)

 

 

 

Let me know if it works. If not then we'll investigate deeper.

 

SilvermarD
Frequent Visitor

Fantastic, no errors with the formula.  Unfortunately, its not producing a result, so something is a miss somewhere.

 

Many thanks

Hi @SilvermarD 

 

Ok firstly it will be good if you take it one step at a time. You should determine if your table result is producing any data at all.

I am not sure about if your filter formula will work but essentially it should look something like this:

 

Filter(colTotalSale, YourDateColumnName >= Date(2022,1,1) && YourDateColumnName <= Date(2022,2,1))

 

The formula above should filter out all items on your date column for the month of Jan 2022.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (4,007)