Finding the first row of a group with multi-row formula

[averyoldakowski]

Hi, I am trying to create this Beginning Cost column using a multirow formula. I would like to take the Current Value at the beginning of every year and assign it to Beginning Cost, however there could be an unknown number of dates in a year, so just using IF [Row-1:Year] != [Year] THEN [Current Value] would not necessarily work unless you write out each row. Is there a better way to do this? 

Ex.

Date	Current Value	Beginning Cost
01-01-2021	10000	10000
01-01-2022	8000	8000
03-01-2022	6500	8000
06-01-2022	6000	8000
01-01-2023	6000	6000

[jordanmount6]

You could create a new column for just the Year of the Date column then use the Sample tool for 1st N Row and group by the year column. Then use your multirow tool

[averyoldakowski]

Would that work if I were looking for the first instance of a year within a group?

Ex.

Unit	Date	Current Value	Beginning Value
1	01-01-2021	10000	10000
1	01-01-2022	8000	8000
1	03-01-2022	6500	8000
1	01-01-2023	6500	6500
2	01-01-2021	15000	15000
2	05-01-2021	7000	15000

[binuacs · Accepted solution]

@averyoldakowski One way of doing this

[averyoldakowski]

Yes that works! Thank you!

[binuacs]

@averyoldakowski Another method using the multi-row tool, If your Date field is not in Alteryx date format convert that into ISO format (YYYY-MM-DD), then use that field in the multi-row tool for the calculation