Measure to get the difference between values of the last two recent dates

[cctanakabr]

Hi,

I'm trying to create a measure that calculates the difference between values of the last date entry and the previous last date entry.

Similar to the post:
https://community.fabric.microsoft.com/t5/Desktop/Finding-the-difference-between-values-on-two-dates...

But I have that grouped by a category (material)

The table (Base) is:

Material    Value    Date
A        20    04/03/2024
B        25    04/03/2024
C        30    04/03/2024
A        20    04/01/2024
B        24    04/01/2024
C        25    04/01/2024
A        19    03/25/2024
B        21    03/25/2024
C        12    03/25/2024

I Will put them on a pivot table (matrix) and the result must be:
       
Material    03/25/2024    04/01/2024    04/03/2024
A        blank        1        0
B        blank        3        1
C        blank        13        5

I've tried something like that

measure
VAR _currDate = MAX(Base[Date])
VAR _previous = CALCULATE (
        MAX (Base[Date]),
        FILTER ( ALLSELECTED(Base), Base[Date] < _currDate )
    )
VAR _mat = Base[Material]
RETURN
    MAX (Base[Value]) - LOOKUPVALUE (Base[Value], Base[Date], _previous, Base[Material]=_mat)

but I'm getting a error

Thanks in advance

[Thennarasu_R · Accepted solution]

@cctanakabr 

Use this calculation to your requirements

Prev Val - Curr Val = Var _AA=LOOKUPVALUE(Measure,'dim date'[Date],'dim date'[Date]-1)

Return

CALCULATE(Measure)-_AA)
It will help to you

Thanks ,
Thennarasu 

If help this answer to you give as accepted a solution

[jgeddes · Accepted solution]

Another approach would be something like...

Measure = 
var _currentSum =
SUM(materialTable[Value])
var _currentMaterial = 
SELECTEDVALUE(materialTable[Material])
var _currentDate = 
SELECTEDVALUE(materialTable[Date])
var _previousDay =
MAXX(
    FILTER(ALL(materialTable), materialTable[Material] = _currentMaterial && materialTable[Date] < _currentDate),
    materialTable[Date]
)
var _previousSum = 
SUMX(
    FILTER(ALL(materialTable), materialTable[Date] = _previousDay && materialTable[Material] = _currentMaterial),
    materialTable[Value]
)
RETURN
IF(
    ISBLANK(_previousDay),
    BLANK(),
    _currentSum - _previousSum
)

[Thennarasu_R · Accepted solution]

@cctanakabr 

Use this calculation to your requirements

Prev Val - Curr Val = Var _AA=LOOKUPVALUE(Measure,'dim date'[Date],'dim date'[Date]-1)

Return

CALCULATE(Measure)-_AA)
It will help to you

Thanks ,
Thennarasu 

If help this answer to you give as accepted a solution

[cctanakabr]

Thanks for your help