DAX that would give me last year's asset from this year's asset

[junding9]

Using 3 columns (Date, Name, This year asset), what DAX via "New column" (not via "New measure") would give me last year asset exactly as below? Please assume that 3 columns (Date, Name, This year asset) are all 'columns' too (not 'measures').

I want a New column named "Last year asset":

Last year asset = ?

Date	Name	This year asset	Last year asset
7/10/2023	Paul	$134	$897
7/10/2022	Paul	$897	$965
7/10/2021	Paul	$965	n/a
4/13/2021	Tony	$322	$468
4/13/2020	Tony	$468	n/a
2/26/2024	Christine	$230	$763
2/26/2023	Christine	$763	$797
2/26/2022	Christine	$797	$865
2/26/2021	Christine	$865	n/a
6/19/2023	Bella	$983	$142
6/19/2022	Bella	$142	$337
6/19/2021	Bella	$337	$47
6/19/2020	Bella	$47	n/a

[Irwan · Accepted solution]

hello @junding9 

please check if this accomodate your need.

Create a calculated column with following DAX:

Last year asset =

var _LastYearDate = MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Date])

Return

CALCULATE(MAX('Table'[This year asset]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=_LastYearDate))

Hope this will help you.

Thank you.

[ThxAlot · Accepted solution]

[Irwan · Accepted solution]

hello @junding9 

please check if this accomodate your need.

Create a calculated column with following DAX:

Last year asset =

var _LastYearDate = MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Date])

Return

CALCULATE(MAX('Table'[This year asset]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=_LastYearDate))

Hope this will help you.

Thank you.