# 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 !

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

… 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) … and I can only hope that I got this right

There are at least 2 ways to do this here

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) .

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

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 !

Glad to know this helped you moving forward !

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