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 with how to do it?  Thank you

  • 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])))

8 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

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

      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.

    • Giavo's avatar
      Giavo
      Helper III
      9 years ago

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

    • 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