Forum Discussion

dirkkoch's avatar
dirkkoch
Helper III
3 years ago
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...
Need Help
Show and Tell
Tips and Tricks
  • MahyarTF's avatar
    MahyarTF
    3 years ago

    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

daXtreme's avatar
daXtreme
Solution Sage
3 years ago

Use Power Query for this because this is where such calculations belong. By the way, the EARLIER function in DAX has been discouraged and you should learn to use variables instead.

Source: https://dax.guide/earlier