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

How to get the total working time per month from Sharepointlist

Hi Community,

 

I've been struggling with an issue in Powerapps. 

I have an PowerApps application with that I save my workingtime to a SP-List. 

In that List I have different columns for example : User, Date, Starttime, Endtime, workingtime and Month-Year.

So what I want to have is an small Dashboard-Gallery that's shows me the total workingtime of every Month in my SP-List for the current User. 

See Screenshot for example

gallery Arbeitszeit BSP.PNG

The Values from the Screenshot above (06.2019 and 07.2019) I get from this formula

Distinct(Zeiterfassung;Mona_Jahr).Result

But actually I'am struggling with the GroupBy and Sum Function 😕

A nice-to-have additional to that would be a comparisson of target and actual workingtime. Therefore I need to know how I can get the number of weeks a month have, with that I can calculate the target workingtime

So maybe someone can help me.

 

Thanks and kind regards

4 REPLIES 4
TheRobRush
Super User
Super User

i don't have time to do the groupby function right now (it's late here, but if noone gets to it by tomorrow I will try to do it then for you) but did want to share this image with you I made for another person. Handy one to have on hand for calculating time differences. (since you have a start/endtime column)

 

 

Capture.PNG

 

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
v-yutliu-msft
Community Support
Community Support

Hi @kbrose ,

Do you want to get the total working time per month?

Try this formula:

AddColumns(
GroupBy(
AddColumns(listname,"month",Month(Date)), //add one column with the month value
"month","detail" //group based on month
),
"sumworkingtime",Sum(detail,workingtime) //add one column with the sum working time of every month
)

Please replace the listname with the name that you use.

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yutliu-msft 

Hi,

sorry for my late answer. I was on holiday.

 

Thanks for your answer but it doesent actually :/.

Add Columns Fehler.PNG

I tried to change the "Month(Date)" part with the "Datum" Column from my SP List, but it didnt work either.

Add Columns Fehler_2.PNG

Here is a snapshot from my SP List. I have all Date informations that I need like (Month, Monthname, Year, Month_Year) but I cant Group it in Powerapps 😞

 

SP List.PNG

 

Maybe you have an idea to solve my problem.

Thanks and kind regards

 

kbrose
Frequent Visitor

okay, I think I got it. But now I have a lot of zeros after my number. Commas are also not displayed. The correct values are (11,5/58,00/29,00) for Aug./Jul./Jun. Does anyone can explain why I have these zeros and no commas?

I want to have it like "8,00 h"

My Functions are:

 

For the Month-Label : GroupBy(Zeiterfassung;"Mona_Jahr";"Items")

For the Workinghour-Label: Value(Sum(Items;AZ))

 

Gallery Sum AZ.PNG

 

Thank and regards

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,235)