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

MahyarTF 

 

THe problem with your solution is that it makes the model unnecessarily bloated (as the index column is not really needed). Second, calculated columns are not compressed the way they are when created in Power Query - this results in even more bloat. In big models this may be unacceptable. Therefore, please use Power Query to do such calculations. Unless, of course, you don't care about how big the model is and how fast DAX could be. But I think you should since you pay money for storage. You also want to have fast DAX and columns optimized for access and calculations.

MahyarTF's avatar
MahyarTF
Memorable Member
3 years ago

Hi daXtreme ,

Thanks for your useful advice.

Just learning and trying to improve my knowledge.

This is the solution it was at top of my head but absolutely there are better solutions.