Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Is there any way to create a new column that shows only the value for each ID based on latest date?
ID | Date | Value | Latest Value |
1 | 4/20/2022 | 8 | 4 |
1 | 4/21/2022 | 2 | 4 |
1 | 4/22/2022 | 4 | 4 |
2 | 5/17/2022 | 9 | 3 |
2 | 5/18/2022 | 5 | 3 |
2 | 5/19/2022 | 3 | 3 |
I have tried this but it doesnt not seem to be working consistently, for some ID it will calculate latest date value but for some it just doesnt output anything
Solved! Go to Solution.
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
latest_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
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?
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
I'm not sure why the volume would make a difference.
I'll take a look at doing it with a measure.
Try this one:
latest_value =
VAR _maxdate =
CALCULATE (
MAX ( 'DATA'[_DATE_] ),
ALLEXCEPT( 'DATA'[ID] )
)
RETURN
CALCULATE (
VALUES ( 'DATA'[Value] ),
'DATA'[_DATE_] = _maxdate,
ALL('DATA')
)
Im not getting the DAX code to work. When I try the full code Its spitting out 'Too few arguments were passed to the ALLEXCEPT function. The minimum argument count for the function is 2.'
I think the error is within ALLEXCEPT function where I can only parse DATA but not 'DATA'[ID].
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |