Power Platform Community Forum Thread Details

Hello, I am quite new to Power Apps but I managed to create some things on my own — now I am a bit stuck.

I am building a small app to manage the capacities of my team. I have to use SharePoint lists, but it works so far. I have a list of my projects and a list with my team members.

In the projects list, every project gets a team member assigned, along with the planned hours, a start date, and an end date. All the projects are quite small and last between one day and a week.

What I have achieved so far is that I can calculate how many hours a team member has to work in a certain time period, so I can check if a person goes over 40 hours a week. That works nicely.

What I want to do now is create a gallery where all my team members are shown with the sum of hours they have in the selected time range. How do I get only one entry per team member in the gallery that shows the total hours across all their assigned projects?

Greetings
Asta

Categories:

Building Power Apps

Hi,

You need to use GroupBy and crate new column with Sum() in it to achieve that

Here is example

Create a colection : colProject

ClearCollect(
    colProjects,
    [
        {ID:1, Title:"Inbound Audit", TeamMember:"John Smith", PlannedHours:8, StartDate:Date(2026,4,1), EndDate:Date(2026,4,1)},
        {ID:2, Title:"Stock Check A", TeamMember:"John Smith", PlannedHours:12, StartDate:Date(2026,4,2), EndDate:Date(2026,4,3)},
        
        {ID:3, Title:"Outbound Prep", TeamMember:"Sarah Jones", PlannedHours:10, StartDate:Date(2026,4,1), EndDate:Date(2026,4,2)},
        {ID:4, Title:"Loading Review", TeamMember:"Sarah Jones", PlannedHours:20, StartDate:Date(2026,4,3), EndDate:Date(2026,4,5)},
        
        {ID:5, Title:"MHE Inspection", TeamMember:"Mike Brown", PlannedHours:16, StartDate:Date(2026,4,2), EndDate:Date(2026,4,4)},
        {ID:6, Title:"Safety Audit", TeamMember:"Mike Brown", PlannedHours:10, StartDate:Date(2026,4,5), EndDate:Date(2026,4,6)},
        
        {ID:7, Title:"Returns Processing", TeamMember:"Emma White", PlannedHours:14, StartDate:Date(2026,4,1), EndDate:Date(2026,4,3)},
        {ID:8, Title:"Damages Review", TeamMember:"Emma White", PlannedHours:18, StartDate:Date(2026,4,4), EndDate:Date(2026,4,6)},
        
        {ID:9, Title:"Picking Optimisation", TeamMember:"David Clark", PlannedHours:22, StartDate:Date(2026,4,2), EndDate:Date(2026,4,5)},
        {ID:10, Title:"Shift Planning", TeamMember:"David Clark", PlannedHours:12, StartDate:Date(2026,4,6), EndDate:Date(2026,4,6)}
    ]
);

Place it to OnVisible property of the screen just to test this concept later you can play on your own SP List

in same place add this code what will transform Project Colection and GroupBY TeamManagert


ClearCollect(
    colTeamCapacity,
    DropColumns(
        AddColumns(
            GroupBy(
                Filter(
                    colProjects,
                    StartDate <= dpEnd.SelectedDate &&
                    EndDate >= dpStart.SelectedDate
                ),
                TeamMember,
                ProjectRows
            ),
            TotalHours,
            Sum(ProjectRows, PlannedHours)
        ),
        ProjectRows
    )
)

now add Date Picker to make sure you can filter Projects by Start and End Date call them dpStart and dpEnd

And add two Galleris in first add colProjects as Iteams property and in secound add colTeamCApacity also add all lables

The result should be like on the screen above but one warning this will be not delagable means after 2000 Project added it will fail to prevent it You can do something like that :

With({ProjectData:Filter(
                    colProjects,
                    StartDate <= dpEnd.SelectedDate &&
                    EndDate >= dpStart.SelectedDate
                )},

ClearCollect(
    colTeamCapacity,
    DropColumns(
        AddColumns(
            GroupBy(
                ProjectData,
                TeamMember,
                ProjectRows
            ),
            TotalHours,
            Sum(ProjectRows, PlannedHours)
        ),
        ProjectRows
    )
))

That will prefilter data but Projects in range need to count less than 2000 rows