Forum Discussion
Solved
Create a new column that only show latest available data
Hi experts I have a data table like so: ID Date Value 1 4/20/2022 8 1 4/21/2022 2 1 4/22/2022 4 2 5/17/2022 9 2 5/18/2022 5 2 5/19/2022 3 ...
Try this measure
zMeasure = VAR _ID = SELECTEDVALUE( 'DATA'[ID] ) VAR _MaxDate = CALCULATE( MAX( 'DATA'[Date] ), FILTER( ALL( 'DATA' ), 'DATA'[ID] = _ID ) ) VAR _LatestValue = CALCULATE( MAX( 'DATA'[Value] ), FILTER( ALL( 'DATA' ), 'DATA'[ID] = _ID && 'DATA'[Date] = _MaxDate ) ) RETURN _LatestValue
grantsamborn
Solution Sage
Solution Sagelatest_value =
VAR _maxdate =
CALCULATE(
MAX( 'DATA'[Date] ),
FILTER(
'DATA',
'DATA'[ID] = EARLIER( 'DATA'[ID] )
)
)
RETURN
CALCULATE(
MAX( 'DATA'[Value] ),
FILTER(
'DATA',
'DATA'[ID] = EARLIER( 'DATA'[ID] )
&& 'DATA'[Date] = _maxdate
)
)
Hi grantsamborn this is what i posted but its not consistent, It will work for some ID but others it wont show the latest
Heres when it works
Then for this ID, the latest value should be '7' from 2019/07/31 but it isnt showing anything
- grantsamborn
If you look at the 2nd last line, I am comparing [ID] whereas you are comparing [Value].
Seems to work for me.
https://1drv.ms/u/s!AnF6rI36HAVkhPIU1yqEYz0MsTNrag?e=BWYssA
Yeh the code I posted isnt working consistently for me, im not sure if its the size of my data (about 22 million rows, with 600,000 unique IDS)
Is there any other method to do the calculation?- grantsamborn
I'm not sure why the volume would make a difference.
I'll take a look at doing it with a measure.