cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AislingFaulkner
Helper II
Helper II

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? 

3 REPLIES 3
rubin_boer
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

rubin_boer_0-1623427659946.png

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.

 

ClearCollect(colExpandedDates,AddColumns(colDates,"Month",Month(date),"Monthname",Text(date,"mmmm"),"Year",Year(date)))

 

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

AddColumns(GroupBy(colExpandedDates,"Monthname","Year","MonthByYear"),"Total",Sum(MonthByYear,Value(runningtime)))

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

rubin_boer_1-1623427928131.png

 

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.

AddColumns(
    GroupBy(
        AddColumns(
            colDates,
            "Month",
            Month(date),
            "Monthname",
            Text(
                date,
                "mmmm"
            ),
            "Year",
            Year(date)
        ),
        "Monthname",
        "Year",
        "MonthByYear"
    ),
    "Total",
    Sum(
        MonthByYear,
        Value(runningtime)
    )
)

 

Hope this helps you

 

 

 

 

gabibalaban
Super User
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.

rubin_boer
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)

rubin_boer_2-1623497293123.png

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)))))

rubin_boer_3-1623497315101.png

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.

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,925)