标准日期表的制作与应用

标准日期表有着广泛的应用,下文将讲解其制作与应用。日期表的制作过程有点像切蛋糕,需要把特定的日子按照西历的逻辑划分到某个月和季度中,因此比较适合通过参数表来构建。接下来,会详细讲解各个参数(表)。

第一个和大家介绍的参数是Month_No, 这个List里面的元素由1到12组成。

第二个和大家介绍的参数是First_Fiscal_Month,这是从Month_No中取出来的一个整数,用来控制每一个财政年的开始月份。

第三个要介绍的参数是First_Day_Of_Week, 数值范围为0到6中的任意一个数字,用来规定每周的第一天

第四个要介绍的参数是First_Date和Last_Date, 这两个参数由ByHand里的A2和A7决定,主要用来规定日期表的第一天和最后一天。

第五个要介绍的参数是Special_Dates, 这个List里面的元素由1900-01-07到1900-01-19组成,主要用来制作参数表Day_Of_Week_No和计算参数Initial_Date。

第六个要介绍的参数是Initial Date, 这个日期会根据First_Day_Of_Week变化,主要用于后续计算Year_Week_Number。

第七个要介绍的参数表为Other_Modes,主要用来制作切片器。

第八个要介绍的参数表为Day_Of_Week_No,这个表会根据Fisrt_Day_Of_Week提供的相对位置,从Special_Dates中取出七个日子,并计算出这些日期的文本形式。

最后一个要介绍的参数表是Final_Mapping,这是最重要的参数表,主要用于构建月份与季度之间关系。以下为这个参数表的代码:

let
    List_Buffering = List.Buffer(Month_No),
    Constant_List =
        List.TransformMany(
            {1..4},
            (w)=˃
                List.Select(
                    List_Buffering,
                    (y)=˃
                        Number.IntegerDivide(
                            y - 1,
                            3
                        ) + 1 = w
                ),
            (x, y)=˃ {y, x}
        ),
    Upper_Part =
        List.LastN(
            Constant_List,
            each _{0} ˃= First_Fiscal_Month
        ),
    Lower_Part =
        List.FirstN(
            Constant_List,
            each _{0} ˂ First_Fiscal_Month
        ),
    Reordering =
        List.Combine(
            {
                Upper_Part,
                Lower_Part
            }
        ),
    To_Table =
        Table.FromRows(
            Reordering,
            type table
                [
                    Month_Number = Int64.Type,
                    Quarter_Number = Int64.Type
                ]
        ),
    Add_Fiscal_Month_Number =
        Table.AddIndexColumn(
            To_Table,
            "Fiscal_Month_Number",
            1
        ),
    Add_Fiscal_Quarter_Number =
        Table.AddColumn(
            Add_Fiscal_Month_Number,
            "Fiscal_Quarter_Number",
            each
                Number.IntegerDivide(
                    [Fiscal_Month_Number] - 1,
                    3
                ) + 1,
            Int64.Type
        ),
    Add_Month =
        Table.AddColumn(
            Add_Fiscal_Quarter_Number,
            "Month",
            each
                Date.ToText(
                    #date(
                        1900,
                        [Month_Number],
                        1
                    ),
                    "MM\.MMM",
                    "US-En"
                ),
            type text
        ),
    Add_Quarter =
        Table.AddColumn(
            Add_Month,
            "Quarter",
            each
                Number.ToText(
                    [Quarter_Number],
                    "\Q0"
                ),
            type text
        ),
    Add_Fiscal_Month =
        Table.AddColumn(
            Add_Quarter,
            "Fiscal_Month",
            each
                Number.ToText(
                    [Fiscal_Month_Number],
                    "\F\M00"
                ),
            type text
        ),
    Add_Fiscal_Quarter =
        Table.AddColumn(
            Add_Fiscal_Month,
            "Fiscal_Quarter",
            each
                Number.ToText(
                    [Fiscal_Quarter_Number],
                    "\F\Q0"
                ),
            type text
        ),
    Add_Offset_For_Fiscal_Year_Number =
        Table.AddColumn(
            Add_Fiscal_Quarter,
            "Offset_For_Fiscal_Year_Number",
            each if
                [Month_Number] ˃ [Fiscal_Month_Number]
            then
                1
            else
                0,
            Int64.Type
        )
in
    Add_Offset_For_Fiscal_Year_Number

代码的逻辑可以简述为:(1)使用List.TransformMany()构建月份与季度之间的映射关系。(2)通过List.FirstN()和List.LastN()根据First_Fiscal_Month对在第一步中产生的复合List进行二分。(3)使用List.Combine()重新整合在第二步中一分为二的复合List。(4)通过Table.FromRows()把第三步的结果转化表格。(5)添加计算列。构建好Final_Mapping后,只要让这个参数表以内嵌表的形式内嵌于每一年,展开后根据年与月之间的关系把日算出来,在添加计算列后就是我们需要的标准日期表。代码如下:

let
    First_Year = Date.Year(First_Date),
    Last_Year = Date.Year(Last_Date),
    All_Years = {First_Year..Last_Year},
    To_Table =
        Table.FromColumns(
            {All_Years},
            type table [Year_Number = Int64.Type]
        ),
    Tbl_Buffering = Table.Buffer(Final_Mapping),
    Tbl_Type =
        Expression.Evaluate(
            Text.Combine(
                {
                    "type table [",
                    Text.Combine(
                        List.Transform(
                            Table.ToRows(
                                Table.SelectColumns(
                                    Table.Schema(Final_Mapping),
                                    {
                                        "Name",
                                        "Kind"
                                    }
                                )
                            ),
                            each Text.Combine(_, "=")
                        ),
                        ","
                    ),
                    "]"
                }
            )
        ),
    Nested_Tbl =
        Table.AddColumn(
            To_Table,
            "nTable",
            each Tbl_Buffering,
            Tbl_Type
        ),
    Expansion_1 =
        Table.ExpandTableColumn(
            Nested_Tbl,
            "nTable",
            Table.ColumnNames(Final_Mapping)
        ),
    Add_Fiscal_Year_Number =
        Table.AddColumn(
            Expansion_1,
            "Fiscal_Year_Number",
            each
                Value.Add(
                    [Year_Number],
                    [Offset_For_Fiscal_Year_Number]
                ),
            Int64.Type
        ),
    Add_Year_Month_Number =
        Table.AddColumn(
            Add_Fiscal_Year_Number,
            "Year_Month_Number",
            each [Year_Number] * 12 + [Month_Number],
            Int64.Type
        ),
    Add_Year_Month =
        Table.AddColumn(
            Add_Year_Month_Number,
            "Year_Month",
            each
                Date.ToText(
                    #date(
                        [Year_Number],
                        [Month_Number],
                        1
                    ),
                    "yyyy-MM\.MMM"
                ),
                type text
        ),
    Add_Fiscal_Year_Month =
        Table.AddColumn(
            Add_Year_Month,
            "Fiscal_Year_Month",
            each
                Number.ToText(
                    [Year_Number] * 100 + [Fiscal_Month_Number],
                    "0000-\F\M00"
                ),
            type text
        ),
    Add_Year_Quarter =
        Table.AddColumn(
            Add_Fiscal_Year_Month,
            "Year_Quarter",
            each
                Number.ToText(
                    [Year_Number] * 10 + [Quarter_Number],
                    "0000-\Q0"
                ),
            type text
        ),
    Add_Fiscal_Year_Quarter =
        Table.AddColumn(
            Add_Year_Quarter,
            "Fiscal_Year_Quarter",
            each
                Number.ToText(
                    [Fiscal_Year_Number] * 10 + [Fiscal_Quarter_Number],
                    "0000-\F\Q0"
                ),
            type text
        ),
    Add_Year_Quarter_No =
        Table.AddColumn(
            Add_Fiscal_Year_Quarter,
            "Year_Quarter_Number",
            each [Year_Number] * 4 + [Quarter_Number],
            Int64.Type
        ),
    Add_nTable =
        Table.AddColumn(
            Add_Year_Quarter_No,
            "nTable",
            each
                let
                    fDate =
                        #date(
                            [Year_Number],
                            [Month_Number],
                            1
                        ),
                    lDate = Date.EndOfMonth(fDate),
                    Date_List =
                        List.Dates(
                            fDate,
                            Duration.TotalDays(lDate - fDate) + 1,
                            #duration(
                                1,
                                0,
                                0,
                                0
                            )
                        ),
                    To_Table =
                        Table.FromColumns(
                            {Date_List},
                            {"Date"}
                        )
                in
                    To_Table,
            type table [Date = date]
        ),
    Expansion =
        Table.ExpandTableColumn(
            Add_nTable,
            "nTable",
            {"Date"}
        ),
    Add_Day_Of_Week =
        Table.AddColumn(
            Expansion,
            "Day_Of_Week",
            each
                Date.ToText(
                    [Date],
                    "ddd",
                    "US-En"
                ),
            type text
        ),
    Inner_Join =
        Table.Join(
            Add_Day_Of_Week,
            "Day_Of_Week",
            Day_Of_Week_No,
            "Day_Of_Week",
            JoinKind.Inner
        ),
    Add_Date_With_Business =
        Table.AddColumn(
            Inner_Join,
            "Date_With_Business",
            each [Date] ˂= Last_Date,
            Logical.Type
        ),
    Add_Year_Week_Number =
        Table.AddColumn(
            Add_Date_With_Business,
            "Year_Week_Number",
            each
                Number.RoundAwayFromZero(
                    Value.Divide(
                        Duration.TotalDays(
                            Value.Subtract(
                                [Date],
                                Initial_Date
                            )
                        ),
                        7
                    ),
                    0
                ),
            Int64.Type
        ),
    
    Col_Selection =
        Table.SelectColumns(
            Add_Year_Week_Number,
            {
                "Date",
                "Year_Number",
                "Month",
                "Month_Number",
                "Year_Month",
                "Year_Month_Number",
                "Quarter",
                "Quarter_Number",
                "Year_Quarter",
                "Year_Quarter_Number",
                "Fiscal_Year_Number",
                "Fiscal_Month",
                "Fiscal_Month_Number",
                "Fiscal_Year_Month",
                "Fiscal_Quarter",
                "Fiscal_Quarter_Number",
                "Fiscal_Year_Quarter",
                "Day_Of_Week",
                "Day_Of_Week_Number",
                "Year_Week_Number",
                "Date_With_Business"
            }
        )
in
    Col_Selection

在制作好标准日期表后,接下来将详细讲解标准日期表在Excel2019中的应用。如下图所示,有一虚构数据包含不同性别的志愿者在2022-01-01到2023-12-31期间在特定日子服用不同药物后的生理指标,现要求使用枢纽表可视化每一天对比上一期(上一天,上一周,上一月,上一季,上一年)的生理指标变化。

由于数据是不连续的,要完成以上要求, 需要定义多个Dax Measure。

如上图所示,第一个需要定义的Measure为SumOfbpVal,公式非常简单意思为对bpVal列进行加总。

因为不是每一个志愿者在每个工作日都有生理数据,所以需要使用AllExcept()屏蔽来自Data_Set表格的影响同时保留Standard_Calendar的作用。

以上为工作日不连续的情况下寻找上一天的解法之一,BI Desktop更新了很多函数,所以一定存在更优的解法,有兴趣的读者可以研究一下。这个解法的思路非常简单,首先对所有的工作日进行降序排序,然后求当前日期的名次,下一个名次对应的日期就是上一天。

如果要求周六与周四或之前的工作日进行比较,需要判断计算出来的Current_Date是不是周六,如果是周六就从最近的周四起往前的五天(假设非工作日最多连续五天)里找最大值,否则从包括临近的上一天起往前的五天里找最大值。

如果要寻找上周的最后一个工作日,需要先根据Quotient(日期 - 1900/01/07 - 1, 7) + 1把当前的Year_Week_Number求出来,然后减一求Year_Week_Number的上一期的值,最后代入求最后一天的公式就可得到答案。

如果要寻找上一个月的最后一个工作日,需要先根据(12 * 年 + 月)把当前的Year_Month_Number求出来,然后减一求Year_Month_Number的上一期的值,最后代入求最后一天的公式就可得到答案。

如果要寻找上一个季度的最后一个工作日,需要先根据(4 * 年 + 季)把当前的Year_Quarter_Number求出来,然后减一求Year_Quarter_Number的上一期的值,最后代入求最后一天的公式就可得到答案。

求上一年最后一个工作日,需要通过Year()求当前年份, 然后减一得到上一年的年份,代入求最后一天的公式就可以得到答案。

为了能在不同颗粒度之间进行切换,需要把不同的选项构造成一个表格,然后通过内连接的方式把切片器当前的选项筛选出来。这个思路会比采用Switch()的思路稍微高效一点。

得到上一期的日期,即可得到上一期的值。

以上为Cur_bpVal的代码,需要算出当天的日子以及考虑Pre_bpVal为空的情况。

以上为Dif_bpVal的代码,意思为当期值减去上一期的值。

要找出一组数据中的异常值,比较简单的做法:首先算出这组数的标准差与均值,然后计算每一个数与均值之差的绝对值再和标准差进行比较,如果绝对值与标准差的比值大于2时,就可以认为这个数在这组数中比较罕有,大于3时,就代表非常罕有的意思。写好公式后,还需要按照下图的流程设置KPI:

最后,需要Remark告诉用户具体是哪两天在进行比较:

附件

5 Replies to “标准日期表的制作与应用”

  1. 谢谢老师的blog,这5年从小白到所谓的“半精通”,这里是我定期吸取养料和再修炼的山洞。这篇文章给我点开了实操的思路,我有一个每日理财净值去计算不同的区间内的年化率的场景非常像,也是需要自己往前找日期,并考虑当天没数据需要微调日期的情况。

发表回复

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