cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

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

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

2 ACCEPTED SOLUTIONS
Responsive Resident

@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
Super User

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
)``````

Proud to be a Super User!

3 REPLIES 3
Super User

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
)``````

Proud to be a Super User!

Responsive Resident

@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
Regular Visitor