cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ViDiBi
Frequent Visitor

Subtract Value from Value of previous row

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

 

DateValue
1.1.202010
1.2.202012
1.3.202013
1.4.202018

 

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

 

DateValueUsage
1.1.2020100
1.2.2020122
1.3.2020131
1.4.2020185

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
GarethPrisk
Resident Rockstar
Resident Rockstar

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.

 

View solution in original post

2 REPLIES 2
GarethPrisk
Resident Rockstar
Resident Rockstar

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.

 

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,549)