Daily to weekly summary of attendance

[Anonymous]

Hi PowerUsers, 

I have daily check-in & check-out data for complete month of workers along with the time they took as break between their shifts, sick leaves and annual leaves.

I want to transform this data into weekly summary, something like this 

I tried using these formulas 

For current week's Monday : DateAdd(Today(),1-Weekday(Today(),StartOfWeek.Monday),Days)

For current week's Friday : DateAdd(Today(),1-Weekday(Today(),StartOfWeek.Monday)+4,Days)

But I'm limited to current week with these formulas, I need all 4 weeks data clubbed out of daily entries. 

Like the sum of clocked hours of data with dates in between the first week of that month, then 2nd week, then 3rd and so on till 4th.

Plus, The idea is to have weekly summary of any month selected.

Looking forward to amazing responses as usual 😄

Regards, 

Ali Nawaz

[Mr-Dang-MSFT]

Hi @Anonymous ,

It sounds like you want to group the days by an integer representing which week of the year they belong to. And from that grouping, aggregate the total stats.

Determining an integer for the week of the year can be found using a calculation like this. Note that this may vary depending on how your company treats the first day of the year--is it the first Sunday of the year or the first Sunday on the same week as January 1.

With(
    {
        selectedDate: Today()
    },
    RoundUp(
        (
            // Calculate the number of days between the selected date and the first Sunday of the year.
            DateDiff(

                // Get the first Sunday of this year.
                DateAdd(

                    // Get the first day of the year.
                    Date(Year(selectedDate),1,1),

                    // Determine the number of days it has been since Sunday.
                    // 1 represents the weekday for Sunday. Change it to 2 if you want the first Monday.
                    1-Weekday(selectedDate)
                ),

                // Selected date
                selectedDate,

                // Get a difference between the dates in units of days.
                Days
            )

            // Compensate by 1 to include the current day.
            +1
        )

        // Divide the number of days by 7 and round it up to determine the nth week of the year.
        /7,
        0
    )
)

This formula above is in isolation--given one date, the "selectedDate", determine what week number it is in.

Let's apply it to every date in a table. The formula below means:

• Add a column to the datasource called "WeekNumber." Make it equal to in integer representing which week of the year a given date belongs to using the calculation described above.
• Then group the table so that records with the same WeekNumber are grouped together in a nested table (aka child table, sub table) called "ByWeek."

GroupBy(
    AddColumns(
        datasource,
        "WeekNumber",
            With(
                {
                    selectedDate: Date
                },
                RoundUp(
                    (
                        // Calculate the number of days between the selected date and the first Sunday of the year.
                        DateDiff(

                            // Get the first Sunday of this year.
                            DateAdd(

                                // Get the first day of the year.
                                Date(Year(selectedDate),1,1),

                                // Determine the number of days it has been since Sunday.
                                // 1 represents the weekday for Sunday. Change it to 2 if you want the first Monday.
                                1-Weekday(selectedDate)
                            ),

                            // Selected date
                            selectedDate,

                            // Get a difference between the dates in units of days.
                            Days
                        )

                        // Compensate by 1 to include the current day.
                        +1
                    )

                    // Divide the number of days by 7 and round it up to determine the nth week of the year.
                    /7,
                    0
                )
            )
    ),
    "WeekNumber","ByWeek"
)

This results in a table with a unique row for each week number. Each row contains:

• a column with the WeekNumber.
• a column called ByWeek that is a table of the records that match the WeekNumber.

From here, you can add more columns for each stat you want. The formula below adds on to the previous: "Add additional columns for each stat that sums up the total clocked hours, break hours, hours to be paid, etc. for each week number."

AddColumns(
    GroupBy(
        AddColumns(
            datasource,
            "WeekNumber",
                With(
                    {
                        selectedDate: Date
                    },
                    RoundUp(
                        (
                            // Calculate the number of days between the selected date and the first Sunday of the year.
                            DateDiff(

                                // Get the first Sunday of this year.
                                DateAdd(

                                    // Get the first day of the year.
                                    Date(Year(selectedDate),1,1),

                                    // Determine the number of days it has been since Sunday.
                                    // 1 represents the weekday for Sunday. Change it to 2 if you want the first Monday.
                                    1-Weekday(selectedDate)
                                ),

                                // Selected date
                                selectedDate,

                                // Get a difference between the dates in units of days.
                                Days
                            )

                            // Compensate by 1 to include the current day.
                            +1
                        )

                        // Divide the number of days by 7 and round it up to determine the nth week of the year.
                        /7,
                        0
                    )
                )
        ),
        "WeekNumber","ByWeek"
    ),
    "TotalClockedHours",Sum(ByWeek,ClockedHours),
    "TotalBreakHours",Sum(ByWeek,AllocatedBreak),
    "TotalSickHours",...,
    "TotalLeaveHours",...,
    "TotalHoursToPay",...
)

Let me know which part you need elaborated.

[Anonymous]

@Mr-Dang-MSFT  Extra ordinary idea to aggregate records on the basis of week of the year 👍

The only ambiguity I have right now is related to the "1st day of the year". 

I am based in Pakistan but the project I am working on is originally for a German based company. In this case, how shall I determine the "1st day of the year" ? 
As far as I know PowerApps adapts the time zone automatically of region in which its operational. 

This is the only concern left here, looking forward to your amazing response 🙂 

Regards, 
Ali Nawaz

[Dorinda]

@Mr-Dang-MSFT 

This works brilliantly, however, it is off a week for what I need, when I put the ISOWeekNum it works great, how do I apply to the code you provide above?

When I apply your code I get this

but the weeks should be 27 and 28 not 28 and 29 based on this

When I put in your sum piece at the bottom for instance for week 27 I have two as my count in SharePoint but it is only showing 1