cancel
Showing results for
Did you mean:
Frequent Visitor

## I want to output the daily cumulative time and create a graph

When you organize your collection, you'll see three columns: a date column, a work column, and a cumulative work column.
The date column contains the specified date range for each day, and the working time column contains the working hours for that day.
This is the main problem.
I would like the cumulative working hours column to be a column that stores the cumulative total of working hours up to that day. I'm having trouble calculating this.
I tried using the formula below, but it doesn't work.
Please tell me if you know.
thank you.

``````ClearCollect(Workcollection,
{ID:1,WorkHours:1,Date:Date(2020,1,1)},
{ID:2,WorkHours:0,Date:Date(2020,1,2)},
{ID:3,WorkHours:0,Date:Date(2020,1,3)},
{ID:4,WorkHours:2,Date:Date(2020,1,4)},
{ID:5,WorkHours:10,Date:Date(2020,1,5)});

Clear(Forgraphs);
ForAll(Sort(Workcollection, Date, SortOrder.Ascending),Collect(Forgraphs,
{Date: Date,WorkHours: WorkHours,
CumulativeWorkHours: Sum(Filter(Workcollection, Date <= Date),WorkHours)}));``````
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Hi @Naomiki,

We can use the AddCollumns function in order to avoid needing a ForAll as a loop. Additionally, we will create an explicit record scope for our Filter function (to distinguish the Date column of Forgraphs and Workcollection).

``````ClearCollect(
Forgraphs,
Sort(
Workcollection,
Date
),
"CumulativeWorkHours",
Sum(
//Create explicit record scope for Workcollection (Inner)
//Fetch all records where the workcollection date is smaller than our current Forgraphs record date
Filter(Workcollection As Inner, Inner.Date <= Date),
WorkHours
)
)
)``````

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

2 REPLIES 2
Solution Supplier

Hi @Naomiki ,

you can try this

``````Clear(Forgraphs);
ForAll(Sort(Workcollection, Date, Ascending), Collect(Forgraphs, {
Date: Date,
WorkHours: WorkHours,
CumulativeWorkHours: Sum(Filter(Workcollection, Date <= Date), WorkHours)
}));``````

If you think my solution is helpful for you, then please accept it. 😊

Super User

Hi @Naomiki,

We can use the AddCollumns function in order to avoid needing a ForAll as a loop. Additionally, we will create an explicit record scope for our Filter function (to distinguish the Date column of Forgraphs and Workcollection).

``````ClearCollect(
Forgraphs,
Sort(
Workcollection,
Date
),
"CumulativeWorkHours",
Sum(
//Create explicit record scope for Workcollection (Inner)
//Fetch all records where the workcollection date is smaller than our current Forgraphs record date
Filter(Workcollection As Inner, Inner.Date <= Date),
WorkHours
)
)
)``````

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Announcements

#### Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.