Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Group dates by month in gallery

Hi guys,


i have a SP list with a Date column called 'DatePurchased'. I have a gallery that filters by employee and shows a running total of all their purchases and that works fine. A new request came in to display running total per month for all purchases, so not filtered by anything. 


How would I go about grouping the cost column by month? 

Super User
Super User

hi @AislingFaulkner seems like you need to group by month and sum the running total. 


Simulating your scenario here with a collection which have a date and value entry

ClearCollect(colDates,ForAll(Sequence(400),{date: Today()-Value, runningtime: Value * Rand() * 10 }))


This will give you a date with a value for four hundred consecutive days like in the gallery below


by adding month and year as columns you will be able to give the summarised total by month for that year. to achieve this i will add three columns to the collection , colDates (month, monthname and year) as a new collection colExpandedDates.




Now lets group by monthname and year and add a running total column, i opted to add this to the Items of the collection


This will give you a gallery with all the month within that year and the running total as below



you can add all in one go as (replace colDates with your SP data source and date with your date column and runningtime with you rrunning time column.



Hope this helps you





hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Dual Super User
Dual Super User

@AislingFaulkner ,

I'm not quite sure that your solution works on large amount of data stored in Sharepoint list, even the employee filter.

Please be aware that Sharepoint returns a limited amount of data to PowerApps (see File -> Settings -> General, max 2000 rows) and if your data exceeds this limit the remaining rows will not be taken in consideration by PowerApps. Of course there are workarounds for this, but they are time / resource consumer that gives a bad user experience.

My solution is to create a new column as calculated column in your Sahrepoint list with formula like:

TEXT([DatePurchased], "mmmm-yyyy")

And inside your app with help of two dropdown lists to let user select the desired month and year and filter the list based on new column. The same principle can be used to filter by employee.

Another solution, if the data isn't very dynamic,  is to create the report in PowerBI and use it in app as a PowerBI tile Control or may be embeded in Sharepoint page directly.

Super User
Super User

hi there @gabibalaban it will work on large sets and the performance is not going to be hit that badly in comparison to collecting the listing without formatting it. furthermore, sometimes users do not have the rights to alter SP lists hence i opted for this solution.


Please find the performance of collecting a large list set to 2000 rows (24 columns), a calculated column will not impact the performance of the list.


Collect 2K list

ClearCollect(colList, SPlist)


Group 2K list ClearCollect(_colList,AddColumns(GroupBy(AddColumns(SPlist,"Month",Month(Created),"Monthname",Text(Created,"mmmm"),"Year",Year(Created)),"Monthname","Year","MonthByYear"),"Total",Sum(MonthByYear,Value(ID)))))


the difference in time is insignificant (2 seconds) and it removes the need to further filter by dropdowns. 


How you collect data is something you need to consider before performing actions on it. I would suggest collecting the data before performing filtering on it. You have options how you choose to perform the grouping and filtering.



hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,275)