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:

IDtimestampvalue
key0120191101150
key0120191031135
key0120191030100
key02201911018
key02201910316
key03201911011450

 

What I want to achieve is adding a calculated column, which would store the increment of value from previous day (based on timestamp)

 

So I would like to get something like this:

IDtimestampvalueincrement
key012019110115015
key012019103113535
key0120191030100null
key022019110182
key02201910316null
key03201911011450null

 

Thanks for your advice in advance!

 

Regards,

Pavel

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.


     

2 Replies

  • 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.


     

  • 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