Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Substract values in same column based on key

Hi, I'm trying to calculate daily increments of a value for each ID and timestamp. This is my as-is situation sample: ID timestamp value key01 20191101 150 key01 20191031 135 key0...
DAX
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try something like this.

    Column = 
VAR __timeStamp = 'Table'[timestamp]
VAR __previousDate = 
    CALCULATE(
        MAX( 'Table'[timestamp] ),
        ALLEXCEPT( 'Table', 'Table'[ID] ),
        'Table'[timestamp] < __timeStamp
    )
VAR __previousValue =
    CALCULATE( 
        SUM( 'Table'[value] ),
        TREATAS( { __previousDate }, 'Table'[timestamp] ),
        ALLEXCEPT( 'Table', 'Table'[ID] )
    )
RETURN IF( __previousValue > 0, 'Table'[value] - __previousValue )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.


     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Column = 
VAR _date = Test[timestamp]
VAR _prevdate = CALCULATE(MAX(Test[timestamp]),FILTER(ALLEXCEPT(Test,Test[ID]),Test[timestamp]<_date))
VAR _prevValue = CALCULATE(SUM(Test[value]),FILTER(ALLEXCEPT(Test,Test[ID]),Test[timestamp]=_prevdate))
RETURN IF(_prevdate<>BLANK(),Test[value]-_prevValue)

Anonymous