DAX Measure and Column Difference between previous date

[Anonymous]

Hello,

I have the below Dataset. And I need both measure and calculated column which calculates the difference as in the below example. I am having problems with the cases when there is gap between dates. Please help.

Date;                ID            Amount            Difference(!)
02/01/01          1                100                     100

06/01/01          2                   5                        5

08/01/01          1                150                       50

09/01/01          2               1500                   1450

16/01/99        1                151                        1

Thank you

[Anonymous · Accepted solution]

I was able to find a solution.

Column =

var rowdate = Sheet[Date]

var prevdate =

CALCULATE(MAX(Sheet[Date]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date] < rowdate))

return

IF(prevdate=BLANK();Sheet[amount];CALCULATE(SUM(Sheet[amount]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date]=rowdate)))-CALCULATE(SUM(Sheet[amount]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date]=prevdate))

Measure =

var rowdate = MAX(Sheet[Date])

var prevdate =

CALCULATE(MAX(Sheet[Date]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date] < rowdate))

return

IF(prevdate=BLANK();SUM(Sheet[amount]);CALCULATE(SUM(Sheet[amount]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date]=rowdate)))-CALCULATE(SUM(Sheet[amount]);FILTER(ALLEXCEPT(Sheet;Sheet[ID]);Sheet[Date]=prevdate))

[v-frfei-msft]

Hi @Anonymous ,

To create a measure as below.

difference =
VAR a =
    MAX ( 'Table'[date] )
VAR d =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] < a ),
        VALUES ( 'Table'[id] )
    )
VAR c =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] = d ),
        VALUES ( 'Table'[id] )
    )
RETURN
    SUM ( 'Table'[amount] ) - c

[Anonymous]

@Anonymous How is difference calculated?