Summarize keeping only the last value for each id

[aguuzdo]

Hello everyone, I'm trying to create a summarized table where I get the latest score for each item. 

My data is like this:

id_item	id_week	score
1	1	90
1	2	70
1	3	80
2	1	40
2	2	60
3	3	30

And, with the use of summarize I would like to accomplish the following table:

id_item	id_week	score
1	3	80
2	2	60
3	3	30

Where there is a row for each item and the score corresponding to the latest id_week that an item have.

Is it possible?

Thanks in advance

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New table =
CALCULATETABLE (
    Data,
    TREATAS (
        GROUPBY (
            Data,
            Data[id_item],
            "@maxidweek", MAXX ( CURRENTGROUP (), Data[id_week] )
        ),
        Data[id_item],
        Data[id_week]
    )
)

[aguuzdo]

Thank you Jihwan, that's working perfectly.
I have another doubt, what would you new to modify if you would like to have another column in the original table showing the score value for of the latest week for each row?

e.g.

id_item	id_week	score	last_score for item
1	1	90	80
1	2	70	80
1	3	80	80
2	1	40	60
2	2	60	60
3	3	30	30

Thanks!

[Jihwan_Kim]

Hi,

thank you for your message.

If you want to create a new column in the table, please check the below picture and the attached pbix file.

It is for creating a new column.

Last_week_score CC =
VAR _maxweeknumber =
    MAXX (
        FILTER ( Data, Data[id_item] = EARLIER ( Data[id_item] ) ),
        Data[id_week]
    )
RETURN
    SUMMARIZE (
        FILTER (
            Data,
            Data[id_item] = EARLIER ( Data[id_item] )
                && Data[id_week] = _maxweeknumber
        ),
        Data[score]
    )

[aguuzdo]

Excelent! thank you very much again.

Have a nice day!