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 🙂
Solved! Go to Solution.
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)
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.
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)
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.
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.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.