ISO 8601 日期表

Dax Patterns很早就给出了使用Dax构建周日期表的方法,但由于目前仍不能在power pivot中使用计算表(Calculated Table),下文将给出使用Power Query按同一思路制作周日期表的方法,方便Excel用户进行与时间有关的计算。制作周日期表涉及多个参数表的制作,考虑到参数表的制作手法不影响最终结果,下文将略过参数表的代码。在讲解思路前,有必要先讲讲ISO 8601 日期表的特征。ISO 8601 日期表是一种以距离1月1日最近(Nearest Mode)的周一为每年第一天的周日期表,每一年会有52周(364天)或者53周(371天)。下文按照4-4-5模式(如果有第53周变为4-4-6模式)对月份和季度进行划分,4-4-5模式指头三个月分别有4周,4周和5周,接下来每三个月重复一次这个规律。

由于1月1日只有7种情况,用来寻找年初和年末的位移表如下:

上表的意思为如果1月1日为周一(Day_Of_Week为0)那么年初就是这天(位移为0),如果1月1日为周五(Day_Of_Week为4)那么年初为三天后的周一(位移为3),如此类推。由于年末为年初的前一天,所以应用于年初的位移减一就是年末的位移。

接下来,读者们需要制作如下颗粒度为周的参数表(可手动输入完成):

这个参数表的Year_Type只有364和371这两种值,Start_Week,End_Week以及FW_Week_Number这三个列的值是一样(1-53)。

完成以上参数表,读者们需要制作以下颗粒度为月的参数表(可手动输入完成):

这个参数表的Year_Type也只有364和371这两种值,从第二列和第三列可以看出左边界和右边界都按照4-4-5或者4-4-6在累加。

完成以上参数表后,读者们需要完成以下颗粒度为季度的参数表(可手动输入完成):

这个参数表的Year_Type也只有364和371这两种值,从第二列和第三列可以看出左边界和右边界都是每次累加13或者14。

为了减少连接的次数,以上三个参数表需要转换为以下结构:

完成转换后,按照合并日期参数表(二)里fuhsian网友提供的方法,可把以上三个表合并为:

完成合并后,需要根据Start_Week推算复合键之一的Day_Of_Fiscal_Year_Number(另外一个为Year_Type)和根据需要添加一些计算列:

至此,所需的参数表就完成了。之后要把有关的日期算出来,首先需要推算大致的年份,这里以第一天为2000-01-01和最后一天为2010-12-31为例:

与位移表连接后,可以得到每一年的开始与结尾:

移除多余的年份后,就可以根据每年的开头和结尾,把复合键(Year_Type和Day_Of_Fiscal_Year_Number)算出来了:

把上表和合并后的参数表连接好后并根据需要添加计算列,就是我们需要的ISO 8601 日期表了:

附件

发表回复

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