Forum Discussion
Add Column Comparing Two Date Columns and Returning the Next date in Column 2 from Column 1
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 |
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.
4 Replies
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.
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.Try doing it in two steps.
Add a column that is just [date_open] and then in the next step fill down that column.
Thanks very much. That worked a treat.
