标准日期表有着广泛的应用，下文将讲解其制作与应用。日期表的制作过程有点像切蛋糕，需要把特定的日子按照西历的逻辑划分到某个月和季度中，因此比较适合通过参数表来构建。接下来，会详细讲解各个参数（表）。
第一个和大家介绍的参数是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告诉用户具体是哪两天在进行比较：
我要哭了。。
群解散了。人都不见了。。
只有你还在坚持分享PQ技术。。
悲伤。怀念。
和大家分享的东西不敢马虎，这样有利于提升自己
谢谢老师的blog，这5年从小白到所谓的“半精通”，这里是我定期吸取养料和再修炼的山洞。这篇文章给我点开了实操的思路，我有一个每日理财净值去计算不同的区间内的年化率的场景非常像，也是需要自己往前找日期，并考虑当天没数据需要微调日期的情况。
输入dax公式的是什么插件吗
PP自带的