月度日期表

月度日期表是颗粒度为月的日期表,主要用来解决会计领域特有的第13个月问题,就实用性来讲价值不大,但却十分适合用来练手,所以和各位分享一下。日期表的制作非常像切蛋糕的过程,所以十分适合通过参数表来构造。以7月为第一个财政月为例,月度日期表有两种情况:

第一种情况是总共有13个财政月,但是普通的月份(Month_Number)仍然只有12个,此时要求第12个财政月和第13个财政月对应的普通月份相同。

第二种情况是总共有13个财政月和13个普通月份,此时要求第13个财政月与第13个普通月份相同。因为存在这两种情况,所以需要Month_For_Key这个列作为桥梁负责连接月度日期表和事实表(Fact Table)。类似于标准日期表,接下将讲解各个参数(表)。

第一个要介绍的参数是First_Fiscal_Month,这个参数为1到12中的其中一个数,用来控制第一个财政月。

第二个要介绍的参数为Months_In_Year,这个参数只能为12或者13用来控制每年的最大财政月, 其他的值似乎没有实际含义。

第三个要介绍的参数是NoOfCalM,用来控制Month_Number中的最大值,这个参数只允许是12或者13,其他的值目前没有想到实际的含义。

第四个要介绍的参数是First_Date,这个值为事实表中最早的日期。

第五个要介绍的参数是Last_Date,这个值为事实表中最大的日期。

let
    Constant_List = {1..12},
    Upper_Part =
        List.LastN(
            Constant_List,
            each _ ˃= First_Fiscal_Month
        ),
    Lower_Part =
        List.FirstN(
            Constant_List,
            each _ ˂ First_Fiscal_Month
        ),
    Month_No_List_BAU =
        List.Combine(
            {
                Upper_Part,
                Lower_Part,
                List.Numbers(
                    13,
                    Months_In_Year - 12
                )
            }
        ),
    Month_No_List =
        if
            NoOfCalM = 12
        then
            List.Alternate(
                List.Repeat(
                    List.Combine(
                        {
                            Upper_Part,
                            Lower_Part
                        }
                    ),
                    Months_In_Year - 12 + 1
                ),
                11,
                1,
                12
            )
        else
            Month_No_List_BAU,
    To_Table =
        Table.FromColumns(
            {
                {1..Months_In_Year},
                Month_No_List,
                Month_No_List_BAU
            },
            type table
                [
                    Fiscal_Month_Number = Int64.Type,
                    Month_Number = Int64.Type,
                    Month_For_Key = Int64.Type
                ]
        ),
    Add_Offset_For_Year_Number =
        Table.AddColumn(
            To_Table,
            "Offset_For_Year_Number",
            each if
                [Month_Number] ˃ [Fiscal_Month_Number]
            then
                -1
            else
                0,
            Int64.Type
        )
in
    Add_Offset_For_Year_Number

第六个要介绍的参数为Full_M_Mapping。对应代码的大意为:(1)利用List.FirstN()和List.LastN()调整1到12月的相对位置;(2)如果是总共有13个财政月,但是普通的月份(Month_Number)仍然只有12个的情形,需要在(1)的结尾重复一次最后一个元素,另一种情形就是需要补上13;(3)不难发现Month_For_Key就是(2)的第二种情形的结果;(4)使用Table.FromColumns()把之前(1),(2)和(3)组合成的复合List转化为表格。

第七个要介绍的参数为Month_Quarter,用来辅助计算财政季度和普通的季度。

第八个要介绍的参数为Full_Mapping,构造过程大致可以描述为:FM_M_Mapping内嵌于Month_Quarter两次,分别求财政季度和普通季度,以及每个月在当前季度中是第几个月,然后添加必要的计算列。

let
    Month_No_For_First_Date = Date.Month(First_Date),
    Year_No_For_First_Date = Date.Year(First_Date),
    Month_No_For_Last_Date = Date.Month(Last_Date),
    Year_No_For_Last_Date = Date.Year(Last_Date),
    
    Mapping_For_First_Month =
        Table.SelectRows(
            Full_Mapping,
            each [Month_Number] = Month_No_For_First_Date
        ),
        
    Mapping_For_Last_Month =
        Table.SelectRows(
            Full_Mapping,
            each [Month_Number] = Month_No_For_Last_Date
        ),
    Cal_First_Fiscal_Month =
        List.Single(
            Table.Column(
                Mapping_For_First_Month,
                "Fiscal_Month_Number"
            )
        ),
    Cal_First_Fiscal_Year =
        Value.Subtract(
            Year_No_For_First_Date,
            List.Single(
                Table.Column(
                    Mapping_For_First_Month,
                    "Offset_For_Year_Number"
                )
            )
        ),
    Cal_Last_Fiscal_Month =
        List.Single(
            Table.Column(
                Mapping_For_Last_Month,
                "Fiscal_Month_Number"
            )
        ),
    Cal_Last_Fiscal_Year =
        Value.Subtract(
            Year_No_For_Last_Date,
            List.Single(
                Table.Column(
                    Mapping_For_Last_Month,
                    "Offset_For_Year_Number"
                )
            )
        ),
    First_Fiscal_Year_Month_Number =
        Value.Add(
            Cal_First_Fiscal_Month,
            Value.Multiply(
                Cal_First_Fiscal_Year,
                Months_In_Year
            )
        ),
    Last_Fiscal_Year_Month_Number =
        Value.Add(
            Cal_Last_Fiscal_Month,
            Value.Multiply(
                Cal_Last_Fiscal_Year,
                Months_In_Year
            )
        ),
    All_Fiscal_Year_Month_Number =
        List.Numbers(
            First_Fiscal_Year_Month_Number,
            Value.Add(
                Value.Subtract(
                    Last_Fiscal_Year_Month_Number,
                    First_Fiscal_Year_Month_Number
                ),
                1
            )
        ),
    Iteration =
        List.Transform(
            All_Fiscal_Year_Month_Number,
            each
                let
                    Repeated_Cal = _ - 1,
                    
                    Result =
                        {
                            _,
                            Value.Add(
                                Number.Mod(
                                    Repeated_Cal,
                                    Months_In_Year
                                ),
                                1
                            ),
                            Number.IntegerDivide(
                                Repeated_Cal,
                                Months_In_Year
                            )
                        }
                in
                    Result
        ),
    To_Table =
        Table.FromRows(
            Iteration,
            type table
                [
                    Fiscal_Year_Month_Number = Int64.Type,
                    Fiscal_Month_Number = Int64.Type,
                    Fiscal_Year_Number = Int64.Type
                ]
        )
in
    To_Table

这是最后的参数表Fiscal_Year_Number, 对应代码的大意:首先根据4*财政年 + 财政月分别计算出First_Date和Last_Date的Fiscal_Year_Number,然后构建以这两个数为首尾的数列,最后把这些数分别代入Mod和Quotient求对应的财政月和财政年。

最后,把Full_Mapping和Fiscal_Month_Number拼接起来就是月度日期表。

附件

One Reply to “月度日期表”

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注