DAX Substract values in same column based on key

[Anonymous]

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
key01	20191030	100
key02	20191101	8
key02	20191031	6
key03	20191101	1450

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:

ID	timestamp	value	increment
key01	20191101	150	15
key01	20191031	135	35
key01	20191030	100	null
key02	20191101	8	2
key02	20191031	6	null
key03	20191101	1450	null

Thanks for your advice in advance!

Regards,

Pavel

[Anonymous]

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

[Mariusz · Accepted solution]

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.