Power Platform Community Forum Thread Details

nsepeWe are building an app that includes financial data for projects. Based on the selected main project, there can be several financial line items that are tied back to an accounting unit. I have successfully built out the monthly values based on month, as shown in the graphic. Each line item includes 60 months (two years back from the current month/year, and two years forward). This monthly horizontal gallery is embedded into a vertical gallery, which is the main gallery for each finance line item. Values are patched to a backend SQL table and the mmyyyy value of that template item is the "key" or "id" of that saved monthly financial unit. Also, each unit contains the Target Value, the Actual Value, and a Forecast Value.

What the business partners want now is a subtotal view that sums each month for each line item, for each Expense Type (either OM / Capital). Something like this:

The actual app screenshot is here:

I got this from CoPilot, but when I bind MonthlySums to the gallery, I can get each row for Expense Type, but I am not sure how to display each Monthly total.

Step 1: Group Records by Category
ClearCollect(
    GroupedRecords,
    AddColumns(
        GroupBy(
            FinanceEntriesForThisWEDB,
            ExpenseType,
            "GroupedItems"
        ),
        "MonthlySums",
        ForAll(
            GroupedItems,
            {
                Month: MonthNumber,
                Year: YearNumber,
                TargetSum: Sum(GroupedItems, Target),
                ActualSum: Sum(GroupedItems, Actual),
                ForecastSum: Sum(GroupedItems, Forecast)
            }
        )
    )
)
Step 2: Sum Monthly Values
ClearCollect(
    MonthlySums,
    AddColumns(
        GroupedRecords,
        "MonthlyTotals",
        ForAll(
            GroupedItems,
            {
                MonthYear: Text(Month) & "/" & Text(Year),
                TargetTotal: Sum(GroupedItems, TargetSum),
                ActualTotal: Sum(GroupedItems, ActualSum),
                ForecastTotal: Sum(GroupedItems, ForecastSum)
            }
        )
    )
)
Step 3: Display Results

Bind your gallery to the MonthlySums collection.
Use labels to display Category, MonthYear, TargetTotal, ActualTotal, and ForecastTotal.

This should help you get the monthly sum totals separated by category for each target, actual, and forecast value. If you need any further adjustments or run into any issues, feel free to let me know!

Categories:

Building Power Apps