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 - __BeforeValAppreciate your Kudos and please mark it as solution if it helps you
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
