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