cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AislingFaulkner
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? 

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

 

 

 

 

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
gabibalaban
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.

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.

 

 

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

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,529)