cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johncao1986
Frequent Visitor

How to sum the values by month and by person

I want to sum the "OT Hours" by "Created by", by "OT Types", by "Status"

 

and if once the "Created by" , "Status" is Approved, Sum of OT hours >48 in the current Month, the submit button is invisible.

 

Can you guide how to set it?

 

Here attached the data source in Sharepoint List

johncao1986_0-1652431837652.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@johncao1986 ,


This will give you the SUM value you want, replace the "VALUES" by your actual values from inputs.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE)
    ),
    'OT Types'
)

 

If you wish to hide your button if the sum of hours > 48  AND only from this month, use below formula on your submit button Visible property. This will work but will probably give delegation. Try changing the dates to variables and it might do the trick.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE And 'Created On' >= Date(
            Year(Today()),
            Month(Today()),
            1
        ) And 'Created On' <= Date(
            Year(Today()),
            Month(Today()) + 2,
            0
        )
    ),
    One
) > 48

 

Best Regards,

Anthony

View solution in original post

4 REPLIES 4
Anthony_Dob
Continued Contributor
Continued Contributor

Can you please provide a screenshot of what you are trying to achieve. Do you just want a Table which has 4 columns -> Created By, Status, OT Types and Sum of Hours?

RusselThomas
Microsoft
Microsoft

Hi @johncao1986 ,

 

How you want to display this will ultimately determine the method as there are a few ways you can do this.

 

That said, you're probably going to want to use a combination of Sum() and GroupBy() functions, so perhaps give those a read.  I agree with @Anthony_Dob - some more info would be required to give you a more concise answer, but perhaps some examples in the meantime;

 

Table grouped by a column:

 

ClearCollect(summaryTable, GroupBy(myDataSource, "Created By", "groupedRowsColumn"))

 

groupedRowsColumn then contains a table of the rows that are grouped by that value.

Created bygroupedRowsColumn

     Joe

OT FromOT EndDining TimesOT Hours
12/5/202212/5/2022323
13/5/202213/5/2022212
    Sue
OT FromOT EndDining TimesOT Hours
14/5/202216/5/2022023
13/5/202214/5/202214

You can adjust this formula to also add a column to sum the hours for the current grouping;

 

With({groupedTable: GroupBy(myCollection, "Created By", "groupedRowsColumn")},  
    Collect(summaryTable, AddColumns(groupedTable, "Total", Sum(ThisRecord.groupedRowsColumn, OT_Hours)))
)

 

summaryTable contents then looks like this

Created bygroupedRowsColumn     Total

     Joe

OT FromOT EndDining TimesOT Hours
12/5/202212/5/2022323
13/5/202213/5/2022212
     35
     Sue
OT FromOT EndDining TimesOT Hours
14/5/202216/5/2022023
13/5/202214/5/202214
     27

You can then use similar logic for your other groupings.

Hope this helps,

RT

Hi @Anthony_Dob @RusselThomas 

 

I use sharepoint list as data source, in my App, I want to show the Total OT Hours this month of the Requestor

 

Can you guide me how to set the formula? Thanks!

johncao1986_2-1652665559917.png

 

 

@johncao1986 ,


This will give you the SUM value you want, replace the "VALUES" by your actual values from inputs.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE)
    ),
    'OT Types'
)

 

If you wish to hide your button if the sum of hours > 48  AND only from this month, use below formula on your submit button Visible property. This will work but will probably give delegation. Try changing the dates to variables and it might do the trick.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE And 'Created On' >= Date(
            Year(Today()),
            Month(Today()),
            1
        ) And 'Created On' <= Date(
            Year(Today()),
            Month(Today()) + 2,
            0
        )
    ),
    One
) > 48

 

Best Regards,

Anthony

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

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

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

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 (2,990)