To group the tasks in the column chart based on weekly durations, you can modify the formula as follows:
ClearCollect(
FilteredTasks,
Filter(
'Task List_1',
Status in ["On Track", "Not yet started", "Pending"] &&
Status <> "Completed"
)
);
ClearCollect(
WeeklyTasks,
AddColumns(
GroupBy(
FilteredTasks,
"Week",
"WeekNumber",
RoundDown((Today() - Created) / 7, 0)
),
"Count",
CountRows(WeekNumber)
)
)
In this updated formula, we first use the Filter function to retrieve the tasks with the desired statuses and exclude the completed ones. We then use the GroupBy function to group the filtered tasks based on their weekly durations.
The RoundDown function is used to calculate the week number by dividing the difference between today's date and the created date by 7. This gives us the number of weeks between the two dates, rounded down to the nearest whole number. This will effectively group the tasks into the desired weekly buckets.
Finally, we use the AddColumns function to add a new column named "Count" to the grouped tasks collection, which represents the count of tasks in each weekly duration.
You can now set the Items property of your column chart to the WeeklyTasks collection and configure the X-Axis, Y-Axis, and Legend properties accordingly.
Make sure to adjust the data source and column names in the formula to match your specific scenario.
