Show the previous month last day 'value' as current month Starting day value

[ineedhelp]

Dataset: 

Area	Type	Code	current_state	date_calc	age_D	age_W	receiving	ending_stock
L4	MI	A	Growing	30-Oct-24	28	5		367
L4	MI	A	Growing	31-Oct-24	29	5		202
L4	MI	A	Growing	1-Nov-24	30	5		37
L4	MI	A	Growing	2-Nov-24	31	5	1200	1,072.00
L4	MI	A	Growing	3-Nov-24	32	5		1,072.00
L4	MI	A	Growing	4-Nov-24	33	5		1,072.00
L4	MI	A	Growing	5-Nov-24	34	5		1,072.00

Hi, so I'm trying to use the last day ending_stock value to be the beginning value of the current month 'begining_stock'.

So when I filter the field 'date_calc' from 1-Nov-24 to 30-Nov-24 then the output will be

Area	Type	Code	current_state	beginning_stock	ex_cacl_1
L4	MI	A	Growing	202	-

I tried using below expression:

FirstSortedValue(If(MonthEnd(AddMonths(date_calc, -1)) = MonthEnd(Today()), ending_stock),
-date_calc)

-> but it's showing null

max({<date_calc = {"=MonthEnd(AddMonths(date_calc, -1))"}>} ending_stock)

-> this one is showing not correct value

[ineedhelp]

any help would be much appreciated

[ineedhelp · Accepted solution]

FirstSortedValue(
{<date_calc = {"$(=MonthEnd(AddMonths(Min(date_calc), -1)))"}>}
ending_stock,
-date_calc
)