Sum and filter collection

(0) Share

Report

Posted on by SilvermarD

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
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)

Was this reply helpful? Yes No
SilvermarD 23 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
SilvermarD 23 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Verified answer

SilvermarD 23 on at

Like (0)

Report
Copy link

Link copied!

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

Many thanks

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No