Forum Discussion
Create measure to calculate the difference in values between latest date and previous date
Hi Looking at the screenshot, I suspect the values in the screenshot were simplified for illustration
If the real expected result for Entity 1 is indeed 7, then there is some additional business logic missing from the example.
Difference Latest vs Previous =
VAR LatestDate =
CALCULATE (
MAX ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[Entity name] )
)VAR PreviousDate =
CALCULATE (
MAX ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[Entity name] ),
Table1[Date] < LatestDate
)VAR LatestValue =
CALCULATE (
SUM ( Table2[Value] ),
Table1[Date] = LatestDate
)VAR PreviousValue =
CALCULATE (
SUM ( Table2[Value] ),
Table1[Date] = PreviousDate
)RETURN
LatestValue - PreviousValueHi Zanqueta , thanks for replying.
Yes I have simplified the data because in reality I have about 300 entities for each date, and 6 dates showing every time. But the logic is the same.
I ended up realising I was using the wrong table in my sources (yes I have the same data in 2 different places...) and I made it work with one of the formulas I was trying previously. Thanks anyway! Your solution worked as well.
This is what I ended up using:
ValueDifference =
VAR LatestDate =
CALCULATE(
MAX('Table1'[Date]),
ALL('Table1')
)VAR PreviousDate =
CALCULATE(
MAX('Table1'[Date]),
FILTER(
ALL('Table1'),
'Table1'[Date] < LatestDate
)
)VAR LatestValue =
CALCULATE(
SUM('Table2'[Value]),
'Table1'[Date] = LatestDate
)VAR PreviousValue =
CALCULATE(
SUM('Table2'[Value]),
'Table1'[Date] = PreviousDate
)RETURN
LatestValue - PreviousValue
Super UserYou can create a measure like
Difference in value =
VAR _Base = SUMMARIZE(
'Table 1',
'Table 1'[Date],
'Table 1'[Entity name],
'Table 2'[Value]
)
VAR _Top1 = INDEX( 1,
_Base,
ORDERBY( 'Table 1'[Date], DESC ),
PARTITIONBY( 'Table 1'[Entity name] )
)
VAR _Top2 = INDEX( 2,
_Base,
ORDERBY( 'Table 1'[Date], DESC ),
PARTITIONBY( 'Table 1'[Entity name] )
)
VAR _LatestValue = SELECTCOLUMNS( _Top1, 'Table 2'[Value] )
VAR _PrevValue = SELECTCOLUMNS( _Top2, 'Table 2'[Value] )
VAR Result = _LatestValue - _PrevValue
RETURN Result