考虑假期的区间匹配

假设今天是2020-01-24，现在有一份记录了从2020-01-25开始到2020-12-31结束的到期货款的表格（BalAmt表格）：

需要设计出以下用于对上表的时间进行区间匹配的参数表（ParaTable表格）:

以上参数的设计需要考虑假期，除了周日，以下日期也纳入假日的定义：

参数表的第一个区间从报表日的下一日（不作任何调整）开始到第一个工作日结束。第二个区间从第一个工作日的下一日（不作任何调整）开始到第二个工作日结束。第三个区间从第二个工作日的下一日（不作任何调整）开始到第七个工作日结束。第四个区间从第七个工作日的下一日（不作任何调整）开始到从报表日开始的第一个月的最后一日结束（这一日对应=Edate(Report_Date,1)的结果）。第五个区间从上一个区间的最后一日的下一日（不作任何调整）开始到从报表日开始的第三个月的最后一日结束（这一日对应=Edate(Report_Date, 3)的结果）。第六个区间从第五个区间的最后一天的第二日（不作任何调整）开始到一个很远的日期（实际计算只需要设定为第六个区间的第一天和BalAmt的最大日期两者之间的最大值）。

要计算工作日，需要定义以下函数fnWorkday()：

( start_date as date, days as number ) as date ⇒
    let
        DateList=
            List.Dates(
                Date.AddDays(
                    start_date,
                    1
                ),
                30,
                #duration( 1, 0, 0, 0 )
            ),
        RemoveHoliday=
            List.Difference(
                DateList,
                Holiday
            ),
        RemoveSu=
            List.Select(
                RemoveHoliday,
                each Date.DayOfWeek( _ ) ‹› 0
            )
    in
        RemoveSu{ days - 1 }

因为按照参数表的要求，只需要计算从报表日开始的七个工作日，只要把从报表日的下一天（不作任何调整）开始到一个很远的日期（fnWorkday()内这一个很远的日期为报表日之后的第30日）中的周日和其他假日排除，取头七天就是需要的结果。

定义好fnWorkday()之后，就可以利用Record.FieldValues()可以模拟R1C1的数值参考（详情请回顾Record.FieldValues()）的特点建立参数表：

let
    TFR=
        Table.Buffer(
            Table.FromRecords(
                Record.FieldValues(
                    [
                        Row1=
                            [
                                StartDate = Date.AddDays( ReportDate, 1 ),
                                EndDate = fnWorkday( ReportDate, 1 ),
                                MaturityBand = "T"
                            ],
                        Row2=
                            [
                                StartDate = Date.AddDays( Row1[EndDate], 1 ),
                                EndDate = fnWorkday( ReportDate, 2 ),
                                MaturityBand = "T + 1"
                            ],
                        Row3=
                            [
                                StartDate = Date.AddDays( Row2[EndDate], 1 ),
                                EndDate = fnWorkday( ReportDate, 7 ),
                                MaturityBand = "T + 2 to T + 6"
                            ],
                        Row4=
                            [
                                StartDate = Date.AddDays( Row3[EndDate], 1 ),
                                EndDate = Date.AddMonths( ReportDate, 1),
                                MaturityBand = "T + 6 to 1M"
                            ],
                        Row5=
                            [
                                StartDate = Date.AddDays( Row4[EndDate], 1 ),
                                EndDate = Date.AddMonths( ReportDate, 3 ),
                                MaturityBand = "1M to 3M"
                            ],
                        Row6=
                            [
                                StartDate = Date.AddDays( Row5[EndDate], 1 ),
                                EndDate=
                                    List.Max(
                                        {
                                            List.Max( BalAmt[Date] ),
                                            StartDate
                                        }
                                    ),
                                MaturityBand = "Over 3M"
                            ]
                    ]
                ),
                type table [ StartDate=date, EndDate=date, MaturityBand=text ]
            )
        )
in
    TFR

建立好参数表后就可以使用以下代码进行区间匹配并进行分组统计：

let
    Matching=
        Table.AddColumn(
            BalAmt,
            "MaturityBand",
            each
                Record.Field(
                    Table.SingleRow(
                        Table.SelectRows(
                            ParaTable,
                            (x) ⇒ [StartDate] ‹= [Date] and x[EndDate] ›= [Date]
                        )
                    ),
                    "MaturityBand"
                ),
            type text
        ),
    Summary=
        Table.Group(
            Matching,
            "MaturityBand",
            {
                "TotalBal",
                each List.Sum( [BalAmt] ),
                type number
            }
        )
in
    Summary