Forum Discussion

Giavo's avatar
Giavo
Helper III
9 years ago
Solved

Col - (Col-1row)

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 ...
  • Giavo's avatar
    Giavo
    9 years ago

    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's avatar
Giavo
Helper III
9 years ago

Giavo's avatar
Giavo
Helper III
9 years ago

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's avatar
    Giavo
    Helper III
    9 years ago

    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's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Giavo
        Helper III
        9 years ago

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