Subtract Value from Value of previous row

[ViDiBi]

Hi everybody!

I'm working on a app where I can add my enegy consumption. So I fill in the consumption value every month. So far everything works but now I'd like to create diagrams with the consumption from month to month. For example

Date	Value
1.1.2020	10
1.2.2020	12
1.3.2020	13
1.4.2020	18

Now I'd like to generate a collection 'Consumption' where the actual consumption is calculated to view these data in the diagram like this:

Date	Value	Usage
1.1.2020	10	0
1.2.2020	12	2
1.3.2020	13	1
1.4.2020	18	5

So Usage should be Value - Value(one month before)

So far I tried a lot but didn't make it 😞

ClearCollect(
    Data_EnergySelected;
    AddColumns(
        SortByColumns(
            Search(
                Data_Energy;
                Dropdown1.Selected.PK_Counter;
                "FK_Counter"
            );
            "Date"
        );
        "Usage";
        1 * Substitute(
            Value;
            ".";
            ","
        ) - 1 * Substitute(
            LookUp(
                SortByColumns(
                    Search(
                        Data_Energy;
                        Dropdown1.Selected.PK_Counter;
                        "FK_Counter"
                    );
                    "Date"
                );
                Date = Date;
                Value
            );
            ".";
            ","
        )
    )
)

Does anyone have an idea how to do this?

Thank you very much for all your help!

Victor

[GarethPrisk · Accepted solution]

Check out this post and sample app I created for another community member who had a similar ask.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Update-multiple-records-in-an-excel-table-on...

Using some of the newer scope formulas, like With, may also be a viable solution. Let me know if you have any questions about the post above, and we'll see if we can make it work.

[ViDiBi]

Hi @GarethPrisk!

Thank you very much for your quick response 🙂

I tried to do it with your formulas and found this video (https://www.youtube.com/watch?v=0xZ4fMJbLvk) where Shane Young explains the ForAll formular and that one should rename a column if you try something like me. That's why I renamed the columns and now it works and the data are shown as expected 🙂

ClearCollect(
    Data_EnergySelected;
    AddColumns(
        SortByColumns(
            Search(
                RenameColumns(
                    Data_Energy;
                    "Date";
                    "Ablesedatum"
                );
                Dropdown1.Selected.PK_Counter;
                "FK_Counter"
            );
            "Ablesedatum"
        );
        "Usage";
        1 * Substitute(
            Value;
            ".";
            ","
        ) - 1 * Substitute(
            First(
                Filter(
                    SortByColumns(
                        Search(
                            Data_Energy;
                            Dropdown1.Selected.PK_Counter;
                            "FK_Counter"
                        );
                        "Date"
                    );
                    DateValue(Text(Date)) = DateValue(
                        Text(
                            DateAdd(
                                Ablesedatum;
                                -1;
                                Months
                            )
                        )
                    )
                )
            ).Value;
            ".";
            ","
        )
    )
)

Thank you very much 🙂

Victor