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.


     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
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.