Sum the difference of a value at two different dates

[Whoule]

Hi,

I am looking to calculate the difference in the "Numberofoutstandingitems" column between the latest date and the one before it. This is updated bi-weekly so it would need to also calculate based on the latest date and the one before it.

Thanks in advance for you help with this.

[danextian]

Hi @Whoule 

You can use EARLIER to access the data from a row other than the current one. Sample formula as per your use case

Difference =
VAR PreviousValue =
    CALCULATE (
        SUM ( Data[Value] ),
        Data[Date] < EARLIER ( Data[Date] ),
        ALLEXCEPT ( Data, Data[Category] )
    ) // ALLEXCEPT to get the value before the current date per category
RETURN
    Data[Value] - PreviousValue

[Whoule]

Hi Danextian,

The code is working but Var PreviousValue is making the sum of every value past the latest one which is not what I would like to do. I would like it to be only the past value so Latest value - previous value. 

[Whoule]

@danextian Do you have an idea on how to do it?

[danextian · Accepted solution]

Hi @Whoule 

is column ID coming from the data source. It could be a good helper column to identify which transaction comes first before the others.

Try this:

VAR PreviousValue =
    CALCULATE (
        SUM ( Data[Value] ),
        Data[Date] = EARLIER ( Data[ID] ) - 1,
        ALLEXCEPT ( Data, Data[Category] )
    ) // ALLEXCEPT to get the value before the current date per category
RETURN
    Data[Value] - PreviousValue