Gallery running tottal

[pnobi]

Hello, trying to create a running total on a gallery.

There may be many tasks in a given month. Would like to show the running task total hours for a given month.

It shows expected text value for

Month < ThisItem.Month

I confirmed that ThisItem.Month returns and integer for the Month (1-12)

I tried this blog Power Apps Running Totals In A Gallery (matthewdevaney.com)

Here is the gallery

Thank you for your help!

[v-mingkz-msft]

Hi @pnobi,

The cause of the error message is the result of Filter function is a table, and the text Label expect a text.

I did some test according to your description.

Following is my month list and Gallery.

You can set the Text property of Text Label as:

Sum(Filter('MonthList', month<=ThisItem.month),Time)

Note that: replace 'MonthList' and 'Time' with you own List name and column name.

Best Regards,

Kyrie

[pnobi]

thank you, I am working on something else but will get back to this in a few days.

[pnobi]

I think probably not the way to go as the "task" is not a number and I wanted to show how many tasks per month. I am looking now into the addcolumns and groupby... thanks anyways

[v-mingkz-msft]

Hi @pnobi,

Could you please offer me some screenshots about your list and 'task' column?

[pnobi]

Here you go. I would like to display for a given month the Total TimeInMin.

I was trying now, to store in my collection, in the patch function, the MonthYear to maybe make this easier and use less functions.
Thanks for your help.

[JatinSaini]

Hi @pnobi ,

Correct me if am wrong.. As per the shared screenshot of the table, you are trying to calculate the Total 'TimeinMin' for a Given Month.

here for a 'given month' means? are you already specifying a month or the current month?

[pnobi]

Sorry, I meant TotalInMin for every MonthYear combo that has tasks, a running total ideally or even a total by MonthYear would do.
thanks 🙂

[JatinSaini]

No problem! To calculate the total time in minutes for every MonthYear combination that has tasks, you can use a combination of GroupBy, AddColumns, and Sum functions in PowerApps. Here's how you can achieve that:

Step 1: Create a new label or text input in your PowerApps app to allow the user to input the desired year.

Step 2: Add a button that the user will click to trigger the calculation.

Step 3: Use the following formula for the button's OnSelect property:

ClearCollect(
FilteredTasks,
Filter(
YourCollectionName,
Year(DueDateColumn) = Value(YearInput.Text)
)
);

ClearCollect(
GroupedTasks,
AddColumns(
GroupBy(FilteredTasks, "MonthYear", "Month(DueDateColumn) & \"/\" & Year(DueDateColumn)"),
"TotalInMin",
Sum(Results, TimeInMinutesColumn)
)
)

In this code:

YourCollectionName should be replaced with the actual name of your collection that contains task information.

DueDateColumn should be replaced with the actual column name in your collection that contains the due date for each task.

TimeInMinutesColumn should be replaced with the actual column name in your collection that contains the time in minutes for each task.

YearInput refers to the input field where the user can specify the desired year.

FilteredTasks is a temporary collection used to store only the tasks that belong to the selected year.

GroupedTasks is another temporary collection used to store the grouped tasks by MonthYear along with the calculated total time in minutes for each group.

Step 4: Finally, you can use a gallery or a data table to display the contents of the GroupedTasks collection, showing the MonthYear and the corresponding TotalInMin for each month-year combination that has tasks.

With this setup, when the user enters a year and clicks the button, PowerApps will filter the tasks for the given year, group them by MonthYear, and calculate the total time in minutes for each MonthYear combination that has tasks. The resulting data will be stored in the GroupedTasks collection, which you can then use to display the information in your app.

[pnobi]

Thank you for that. I am pretty sure that is pretty close to how this is solved, and I just lack the knowledge.
How about a running total in red like so:

Would I need a second gallery? thanks again