new column with the value of the last date

[Syndicate_Admin]

Hello everyone!!

I'm wanting to add a column to my table and in this column I want to get the value it gives me on the last date filtered with id.

I have an id column, a value column, a date column, and I want in the new column me the value that is the most recent date. Kind of:

I want to get the last column. of id B34001 its last date was 07/12/2020 and by that date the value ea 0.96 then in my new column I want in all id B34001 to come out 0-96.

could you?

thank you by suede hand

Best regards

[Syndicate_Admin]

Thank you so much I worked perfectly.

Best regards

[Anonymous]

Hi @Syndicate_Admin 

Use

Column1 = CALCULATE(MAX('table'[date],ALLEXCEPT('table', 'table'[id])) 

to create a new column

[Syndicate_Admin]

Hello

doesn't accept it tells me "You can't determine a single value for the 'date' column in the 'Knobs' table. This can happen when a measure formula references a column that contains many values without specifying an aggregation at least, maximum, count, or sum to get a single result."

and I don't see that this is the value that is what I want to be reflected in the new column.

I don't know if I explain myself well. of the image I copy I want to create that last column (last value) in my table in power bi

Thank you

[Anonymous · Accepted solution]

Don'@Syndicate_Admin,

You can create a calculated column or measure to get the value of the most recent date:

1. Calculated column

ultimo valor = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[fecha] ),
        FILTER ( 'Table', 'Table'[id] = EARLIER ( 'Table'[id] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[valor] ),
        FILTER (
            'Table',
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[fecha] = _maxdate
        )
    )

2. Measure

Measure = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[fecha] ),
        FILTER ( ALL('Table'), 'Table'[id] = MAX( 'Table'[id] ) )
    )
RETURN 
    CALCULATE (
        MAX ( 'Table'[valor] ),
        FILTER (
           ALL( 'Table'),
            'Table'[id] = MAX(  'Table'[id] )
                && 'Table'[fecha] = _maxdate
        )
    )

Best regards

[Syndicate_Admin]

Hello

I work by creating the column but I have some data that in the value is blank, then in the last value column some are blank. how can I do so they don't take the values that are blank?

Thank you

[Syndicate_Admin]

Hello

now I have another problem, it works well for me unless the last value is blank if it blank puts me white. I'd like you to give me the last value that's not white, how could it be done?

for now I just have: would I have to place lastnonblankvalue?

ultimo valor = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[fecha] ),
        FILTER ( 'Table', 'Table'[id] = EARLIER ( 'Table'[id] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[valor] ),
        FILTER (
            'Table',
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[fecha] = _maxdate
        )
    )