Show prev record in current row in staright table

[Dinesh_123]

Refering to table attached I want a previous designation of against each employeeid along their current designation wherever Ispromotion Flag is 1.

EmployeeID	DateID	IsPromotion	FactFlag	DesignationName
351	1-31-2022	0	Fact	Analyst
351	2-28-2022	0	Fact	Analyst
351	3-31-2022	0	Fact	Analyst
351	4-30-2022	0	Fact	Analyst
351	5-31-2022	0	Fact	Analyst
351	6-30-2022	0	Fact	Analyst
351	7-31-2022	0	Fact	Analyst
351	8-31-2022	1	Fact	Manager

It should show a table like following, I just want row where ispromotion is 1.

i Have tried above, set expression, p, e and only . Please suggest some ways to achieve this .

Note*: I want this on front end not through script.

EmployeeID	DateID	IsPromotion	FactFlag	Current DesignationName	Prev Designation
351	8-31-2022	1	Fact	Manager	Analyst

[Gabbar · Accepted solution]

Try This:

Use this as Dimension:- =Coalesce(aggr(Above(DesignationName),(EmployeeID,numeric,Ascending),(DateID,numeric,Ascending)),DesignationName).

And Use this as Measure:-  Count({<IsPromotion={'1'}>}EmployeeID).

[Dinesh_123]

Thanks Akash.