M-Query or DAX? - find last value from first day of data entry

[SevsBo]

I have a log of changes that took place for a series of items. I need to determine what the last change was and to what value it was set on the first day of changes being made for each individual item.

Table with expected results outlined below.

Item name	Datetime US (simplified here)	Original Value	New Value
Item1	01/01/24 09:00	null	100
Item1	01/01/24 10:00	100	50
Item1	02/01/24 09:00	50	200
Item 2	02/01/24 09:00	null	1000
Item 2	03/01/24 12:00	1000	2000
Item 3	05/01/24 10:00	400	300
Item 3	05/01/24 10:01	300	500
Item 3	06/01/24 09:00	500	550
Item 4	01/01/24 11:00	null	750

I have a good idea of how to do this in SQL but as this data comes straight from source I need to resort to using either DAX or M-Query for this task, and I would appreciate any help to achieve this!

[v-kongfanf-msft]

Hi @SevsBo ,

Did @ThxAlot reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[ThxAlot · Accepted solution]

For fun only, to showcase the power of Excel formulas,