Power Query: Add column of previous value with condition

[seppel123]

Hi all, 

i am having some trouble with my Power BI report. I am looking at employee data to analyze different status types, e.g. holidays, sickness. I am interested in patterns, e.g. how many sickness cases with how many sick days there were for each employee over the last 5 years. Since there are more then 50 employees this table gets rather big. 

Here are some examples of the table to get an idea what i want: 

1) 24/7 Shift System - not every employee works on every day, but everyday someone is working:

	Names
	Day	Night
01.01.	1+2	7+8
02.01.	3+4	1+2
03.01.	5+6	3+4
04.01.	7+8	5+6
05.01.	1+2	7+8
06.01.	3+4	1+2

2) Status Data (already added are the columsn PrevStatus and NextStatus with a highlighted section of the desired outcome)

Date	Name	Number	Status	PrevStatus	NextStatus
01.01.2023	Name1	10001	Present	null	Present
01.01.2023	Name2	10002	Present	null	Present
02.01.2023	Name3	10003	Sick	null	Present
02.01.2023	Name4	10004	Present	null	Present
03.01.2023	Name5	10005	Holiday	null	Present
03.01.2023	Name6	10006	Present	null	Present
01.01.2023	Name7	10007	Present	null	Present
01.01.2023	Name8	10008	Holiday	null	Present
02.01.2023	Name1	10001	Present	Present	Sick
02.01.2023	Name2	10002	Present	Present	Present
03.01.2023	Name3	10003	Present	Sick	Present
03.01.2023	Name4	10004	Present	Present	Present
04.01.2023	Name5	10005	Present	Holiday	Holiday
04.01.2023	Name6	10006	Present	Present	Present
04.01.2023	Name7	10007	Present	Present	Present
04.01.2023	Name8	10008	Present	Holiday	Present
05.01.2023	Name1	10001	Sick	Present	Present
05.01.2023	Name2	10002	Present	Present	Holiday
06.01.2023	Name3	10003	Present	Present	Present
06.01.2023	Name4	10004	Present	Present	Present
07.01.2023	Name5	10005	Holiday	Present	Present
07.01.2023	Name6	10006	Present	Present	Present
05.01.2023	Name7	10007	Present	Present	Present
05.01.2023	Name8	10008	Present	Present	Present
06.01.2023	Name1	10001	Present	Sick	null
06.01.2023	Name2	10002	Holiday	Present	null
07.01.2023	Name3	10003	Present	Present	null
07.01.2023	Name4	10004	Present	Present	null
08.01.2023	Name5	10005	Present	Holiday	null
08.01.2023	Name6	10006	Present	Present	null
08.01.2023	Name7	10007	Present	Present	null
08.01.2023	Name8	10008	Present	Present	null

So far i did this with a calculated column in Power BI, but since my import got too big i am getting the error that there is not enough RAM. I tried some changes in the options (max. number of  parallel queries, max. RAM per query) but it didnt resolve, so i wanted to solve this by addid a column via Power Query instead of a calculated column. 

The code for the calculated column is as follows:

PrevStatus = 

var index = Table[Index]
var previndex = CALCULATE(max('Table'[Index]),ALLEXCEPT('Table','Table'[Number]),Table[Index]<index)

return
CALCULATE(max('Table'[Status]),ALLEXCEPT('Table','Table'[Number]),'Table'[Index]=previndex)

Any advice on how to do this? 

Best regards

Sebastian

[seppel123]

Perfect, that worked! Thank you so much. 
Interesting way with the grouping, i will look into that

[Ahmedx · Accepted solution]

pls try M Code