Col - (Col-1row)

[Giavo]

Hello all,

i have a column with cumulative numbers inside. As i don't want it cumulative but just the delta, i thinked of substraction between the value and value-previous(date). Can you help me with how to do it?  Thank you

[Giavo]

[Giavo · Accepted solution]

Hello , i'm posting the solution to my problem so it can be usefull to others in the future:

Delta= IF(ISBLANK(CALCULATE(MAX([VALUE]),

ALL(Table),Table[PROJECT ID]=

EARLIER(Table[PROJECT ID]),Table[DATE]<EARLIER(Table[DATE]))),0,

[VALUE]-CALCULATE(MAX([VALUE]),ALL(Table),Table[PROJECT ID]=EARLIER(Table[PROJECT ID]),Table[DATE]<EARLIER(Table[DATE])))

[Giavo]

Hello all,

i have a column with numbers and it is cumulative. As i don't want it in a cumulative form but just the delta between the rows, i wanted to simply substract the same column with the previous row to have just a delta. Any idea of how to do it ? Thank you all

[v-huizhn-msft]

Hi @Giavo,

If you just substract the previous rows, you can add an index column in Power Query by clicking "Add index column" under "Add column" on home page. Then you can create a calculated column using the formula.

NewColumn=Table[Column]-LOOKUPVALUE(Table[Column],Table[index],Table[index]-1)

In addition, I am not clear your requirement: I don't want it in a cumulative form but just the delta. Could you please share more details, or list sample table and expected result?

Best Regards,
Angelia

[Giavo]

Thank you for your reply, i added some more clear details of my problem as you can see

[Giavo]

EDIT: and per every stage i have a PLANNED DATE column. So insted of INDEX column, is it possible to calculate the delta column with PLANNED DATE(earlier) and not with INDEX(earlier) ?

[Phil_Seamark]

Hi @Giavo

If you have an index column this is pretty easy using the following calculated column.  If you don't have an index column you can add one in the Query Editor using the add index column feature.

Delta Column = 
        'Table3'[Actual] - CALCULATE(
                                    MIN('Table3'[Actual]),
                                    FILTER(
                                         ALL('Table3'),
                                        'Table3'[Index] = EARLIER('Table3'[Index]) -1)
                                        )

[Giavo]

Thank you for the reply, it works with your data. But not with mine. I have a table with 10K rows. PROJECT ID and VALUE(cumulative). Per every PROJECT ID i have different stages. So adding just an index column doesn't solve the proble because i guess the order is not always from 0 to 10000. It can be different.