Add Column Comparing Two Date Columns and Returning the Next date in Column 2 from Column 1

[MSMK]

I am customizing my date table for my business. I have built a column containing only the dates we are open. As we are closed on the weekend I have null values for Saturday, Sunday and holiday closures.

I need a third column added to my date table containing the current business day if open or if closed, the next day we're open.

I have tried Table.Min with little success. I have tried MINX with filters for open_date<> null and open_date>=Date.Date. I keep getting circular reference errors.

This is the result I'm looking for. Thank for looking.

Date.Date	date_open	Result
12/31/2026	12/31/2026	12/31/2026
12/30/2026	12/30/2026	12/30/2026
12/29/2026	12/29/2026	12/29/2026
12/28/2026	12/28/2026	12/28/2026
12/27/2026		12/28/2026
12/26/2026		12/28/2026
12/25/2026	12/25/2026	12/25/2026
12/24/2026	12/24/2026	12/24/2026
12/23/2026	12/23/2026	12/23/2026
12/22/2026	12/22/2026	12/22/2026
12/21/2026	12/21/2026	12/21/2026
12/20/2026		12/21/2026
12/19/2026		12/21/2026
12/18/2026	12/18/2026	12/18/2026
12/17/2026	12/17/2026	12/17/2026
12/16/2026	12/16/2026	12/16/2026
12/15/2026	12/15/2026	12/15/2026
12/14/2026	12/14/2026	12/14/2026
12/13/2026		12/14/2026
12/12/2026		12/14/2026

[MSMK]

Thanks very much. That worked a treat.

[MSMK]

Thank you for looking.

Table.AddColumn(#"Removed other columns", "result", each Table.FillDown(table name, [date_open]))

Result was an error:

Expression.Error: A cyclic reference was encountered during evaluation. 

That's the error I've seen a lot so I must be doing something fundamentally off. The table I'm using is the name of the table.

[jgeddes]

Try doing it in two steps.
Add a column that is just [date_open] and then in the next step fill down that column.

[jgeddes · Accepted solution]

You may be able to add the column referencing the [date_open] column and then doing a Table.FillUp() (or Table.FillDown() depending on which way the table is sorted) step to the new column.