How to get the total working time per month from Sharepointlist

[kbrose]

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

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

[TheRobRush]

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)

[v-yutliu-msft]

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

[kbrose]

@v-yutliu-msft 

Hi,

sorry for my late answer. I was on holiday.

Thanks for your answer but it doesent actually :/.

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

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 😞

Maybe you have an idea to solve my problem.

Thanks and kind regards

[kbrose]

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

Thank and regards