Calculate Increase from previous row

Hi Community,

I have a table where I track the increase of work hours on projects

I’m trying to calculate the increase of used hours from previous used hours column based on project column and date.
I tried to add a row index column and calculate based on it, but I’m not able to accomplish what I need

Can someone guide me how to do that

Thank you.

Hi George,

There are several examples of this already in the community.

I also have an example in my doc Rambling Pete’s quick examples, which is available in the gallery

P
(From my phone)

Hi @George_Akkawi and Welcome to the Community :partying_face: !

As you already have a date and the dates seem to be unique per project, I’m not sure you really need an index here :blush:

… And I’ll admit that I’m not 100% sure of what you were trying to accomplish here so I assumed you wanted to determine the effective difference between the [Used Hours] sitting in thisRow and the one sitting in the previous row (per project and dates) :blush: … and I can only hope that I got this right :sweat_smile:

There are at least 2 ways to do this here :blush:

The “all-in-one” which I’ve done in the sample you’ll found below in the fields Increase and [Increase %] … or using a relation field to find the previous row in the table (if any) and access its value of [Used Hours] to calculate the Increase and [Increase %] which I’ve done with the fields Table, [Increase 2] and [Increase % 2].

The formula I used in the field Increase is this one:

thisRow.[Used Hours] - Table.Filter(
  Project = thisRow.Project AND Date < thisRow.Date
).Sort(true,Table.Date).Last().[Used Hours]

… where:

Table.Filter(
  Project = thisRow.Project AND Date < thisRow.Date
).Sort(true,Table.Date).Last().[Used Hours].IfBlank(thisRow.[Used Hours])

Creates the sorted list of all the previous rows per project from which I only keep the last one and access its [Used Hours] (and if the Filter() doesn’t return anything meaning that there’s no previous row, it will use the value within thisRow.[Used Hours] so there would be no increase) :blush: .

This Filter() formula is very similar to the one used in the single select relation field Table

After that, if you really still think an Index could be useful at some point, the “Index” road is explored with the fields Index, [Increase 3] and [Increase % 3]

I hope this helps a little :innocent:

3 Likes

Actually this helps a lot! thank you soooooooo much for this well detailed answer!
I went with the first “Increase” as you are totally right, the index is not really needed in this case

My pleasure @George_Akkawi :blush: !

Glad to know this helped you moving forward :raised_hands: !

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.