Forum Discussion
Solved
Dynamically subtract two values in same column between two dates
Hi, I have two columns (one with values, one with dates, see Screenshot marked red). I want to create a calculated column which subtracts each value with the one from the former date (see desired...
Hi,
I did as below :
1- Add the Index column to the table in Power Query.
2- Then use below code for create the new column :
DiffVal Col =
Var __CurrIndex = Sheet57[Index]
Var __CurVal = (Sheet57[Value])
Var __BeforeVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __CurrIndex - 1 ))
return __CurVal - __BeforeVal
Appreciate your Kudos and please mark it as solution if it helps you
MahyarTF
Thanks! Yes, that would work if there is a value in each row of the column. Unfortunately there might be some blanks, meaning that if there is no value, it should substract the actual with the last availabe value in the column
Hi dirkkoch
Please try the below code in the new column I described earlier :
DiffVal Col =Var __CurrIndex = Sheet57[Index]Var __PreviousIndex = CALCULATE(max(Sheet57[Index]), all(Sheet57), Sheet57[Index] < __CurrIndex, not(ISBLANK(Sheet57[Value])))Var __BeforePreviousIndex = CALCULATE(max(Sheet57[Index]), all(Sheet57), Sheet57[Index] < __PreviousIndex, not(ISBLANK(Sheet57[Value])))Var __PreviousVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __PreviousIndex))Var __BeforePreviousVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __BeforePreviousIndex))Var __NextVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __CurrIndex+1))Var __CurVal = if(ISBLANK(Sheet57[Value]), __NextVal,Sheet57[Value])return if(ISBLANK(Sheet57[Value]), __PreviousVal - __BeforePreviousVal, __CurVal - __PreviousVal)Appreciate your Kudos and please mark it as a solution if it helps you