Multi-Row Formula Expression

[MarkPitcock]

I'm struggling to create an expression.

Using a multi-Row formula tool the desired result I am looking for is to create new field : DayCount 

When SettleDate row match then the day count should be 1 then the next pair 2 then the next pair 3 etc. 

Thank you for your time and help 

Business Days	SettleDate	Day	DayCount
3	12/11/2021	Friday	1
3	12/11/2021	Friday	1
3	15/11/2021	Monday	2
3	15/11/2021	Monday	2
3	16/11/2021	Tuesday	3
3	16/11/2021	Tuesday	3
3	17/11/2021	Wednesday	4
3	17/11/2021	Wednesday	4
3	18/11/2021	Thursday	5
3	18/11/2021	Thursday	5

[Luke_C · Accepted solution]

Hi @MarkPitcock 

Here's two ways to do this:

1. Multi-row formula:
   1. The below formula will do the check. I set the value of rows that don't exist as null in the configurations. The first record will be set to 1 based on that, then the rest does the logic you describe.

if isnull([Row-1:DayCount]) then 1
elseif [Day]=[Row-1:Day] then [Row-1:DayCount]
else [Row-1:DayCount]+1
endif

.2. Summarize data (or remove dupes), add a record ID, and join back to the original data set. 

[MarkPitcock]

Thank you Luke_C - works like magic