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.
