Display previous profit value from non-concurrent dates across multiple categories

[David1780]

Hello Team,

I am looking for a DAX formula that looks up the most recent previous profit figure of a project. The dates are not sequentual. This has been covered in a previous post linked below but my data set includes profit from multiple projects. I am looking to see the previous profit from the same project. Data looks like this:

Project	Date	Profit
222222	15/09/2022	$          1,500.00
222222	15/10/2022	$          1,500.00
111222	1/11/2022	$             100.00
8787878	14/11/2022	$               50.00
111222	15/11/2022	$             100.00
222222	15/11/2022	$          1,500.00
8787878	24/11/2022	$             100.00
8787878	11/12/2022	$             110.00
222222	15/12/2022	$             500.00
111222	21/12/2022	$             120.00
8787878	23/12/2022	$             190.00
222222	15/01/2023	$             500.00
8787878	15/01/2023	$             555.00
111222	1/03/2023	$             120.00

I have manually generated the expected results in the following table:

Project	Date	Profit	Previous
222222	15/09/2022	$ 1,500.00
222222	15/10/2022	$ 1,500.00	1500
111222	1/11/2022	$    100.00
8787878	14/11/2022	$      50.00
111222	15/11/2022	$    100.00	100
222222	15/11/2022	$ 1,500.00	1500
8787878	24/11/2022	$    100.00	50
8787878	11/12/2022	$    110.00	100
222222	15/12/2022	$    500.00	1500
111222	21/12/2022	$    120.00	100
8787878	23/12/2022	$    190.00	110
222222	15/01/2023	$    500.00	500
8787878	15/01/2023	$    555.00	190
111222	1/03/2023	$    120.00	120

Here is a link to a similar question:

https://community.powerbi.com/t5/Desktop/Previous-Value-Using-DAX/m-p/933746

Thanks in advance.

[ryan_mayu]

@David1780 

you can try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
return if(ISBLANK(_last),blank(),maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]=_last),'Table'[ Profit ]))

[David1780 · Accepted solution]

Slight adjustment to the last part of the formula.

Column = 
VAR _last=maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
return if(ISBLANK(_last),blank(),maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]=_last),'Table'[Profit]))