Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
VV1026
New Member

Create a new column that only show latest available data

Hi experts I have a data table like so:

IDDateValue
14/20/2022   8
14/21/2022   2
14/22/2022   4
25/17/2022   9
25/18/2022   5
25/19/2022   3

 

Is there any way to create a new column that shows only the value for each ID based on latest date?

IDDateValueLatest Value
14/20/2022   8   4
14/21/2022   2   4
14/22/2022   4   4
25/17/2022   9   3
25/18/2022   5   3
25/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

latest_value =
VAR _maxdate =
    CALCULATE (
        MAX ( 'DATA'[_DATE_] ),
        FILTER ( DATA, 'DATA'[ID] = EARLIER ( 'DATA'[ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'DATA'[Value] ),
        FILTER (
            'DATA',
            'DATA'[Value] = EARLIER ( 'DATA'[Value] )
                && 'DATA'[_DATE_] = _maxdate
        )
    )
1 ACCEPTED 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

View solution in original post

8 REPLIES 8
grantsamborn
Solution Sage
Solution Sage

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 

VV1026_0-1672872691494.png

 

Then for this ID, the latest value should be '7' from 2019/07/31 but it isnt showing anything

VV1026_1-1672872746138.png

 

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.

 

grandtotal
Resolver III
Resolver III

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].

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.