DAX/PowerBI系列 - 关于时间系列 - 如何用脚本生成时间维度 (Generate Date Dimension)

跟大家的交流是我的动力。 :)

 

 

Update 2019/Jul/11：增加如何生成时间维度表（时刻分钟）文章链接
Update 2017/Oct/06：更新了生成日期为度的脚本- #"Changed Type"

Update 2017/Sep/21 ：来信的一些童鞋都用事实表（Fact Table）来作为时间维度的计算，对于简单的计算并没有问题，但是对于复杂的涉及到更多的FilterContext的transition的时候，这样的设计往往就不能实现，所以强烈建议单独建一个时间维度表（Date Dimension）---- 这个涉及到一些些data warehousing的一些基础知识。

还等什么，往下瞅瞅！！

 

DAX/PowerBI系列 - 关于时间系列 - 如何用脚本生成时间维度 (Generate Date Dimension)

难度： ★☆☆☆☆（1星）

适用范围： ★★★★★（5星）

这个时间系列想写很久了，今天开始走一小步。也是作为后续关于时间计算文章的基础。    （文末发一个小福利。 ）

 

概况：

关于时间序列的计算是一个很（也）常（很）用（大）的topic，而且应用范围很广，譬如计算同比，环比，根据时间序列预测某个值。必不可少的就是在模型里面有一个日期的维度。

PowerBI提供了一个AutoCalendar的函数来生成一个日期的时间表，很好！但它仅此生成一个日期列。

可素，往往我们需要很多其他的日期属性列，譬如：单独的年，月，日，季度，财年，等等。我们可以添加计算列，把它们一个一个加进来，但是我另外一个model也需要这个时间维度表呢？

重复添加计算列，这个活真的比较无趣。有没有什么好方法去避免呐？

这个就是今天的主题：如何用脚本生成时间维度。(How to use script to generate date dimension)

 如何生成时刻分钟维度的文章，请戳这里。

 

应用场景：

以下是几个应用场景：

• 复用时间维度表
• 偷懒不想一个个添加时间属性列
• 分析时间序列
• 分析同比环比等需要一个日期维度

 

 

 

最终PowerBI效果显示如下：

 

 

要点：

 按下面步骤操作，具体Ｍ语言是什么，可以忽略。（下面的属性不够用再参考Ｍ语言是什么）

 

 1.PowerBI面板>Edit Query进入Qery Editor> New Source > Blank Query>

 

 

2. Advanced Edictor，贴入文末脚本并保存。

 

 

 

 

3.填入参数，点[Invoke]

 

 4.哇啦，你得到一个时间维度表啦，(*^__^*) 嘻嘻……

 

 

脚本如下：（拿走，不谢）

脚本使用Ｍ语言写的，如果想修改添加其他的列，参考一下Ｍ语言。　（又一种语言，╮(╯▽╰)╭）

(StartDate as date, YearsToAppend as number, FinancialYearStartingMonth as number ) =>
let
    
    YearsToAppend =  YearsToAppend, 
    FinancialYearStartingMonth=  FinancialYearStartingMonth, 
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), Date.Day(StartDate)),
    // Generate base table
    Source = List.Dates(Date.From(StartDate),YearsToAppend*365,#duration(1, 0, 0, 0)),
    Transformed = List.Transform(Source, each Date.ToRecord(_)),
    Tabled = Table.FromList(Transformed,Record.FieldValues,{"Year","Month","Day"}),
    //Add Full Date Column
    AddDateKey = Table.AddColumn(Tabled,"Date",each Date.FromText(Text.From([Year])&"-"&Text.From([Month])&"-"&Text.From([Day]))),
    //DateBKeyConvert = Table.TransformColumnTypes(Date,{{"Date", type date}}),
    DateKeyAdded = Table.AddColumn(AddDateKey,"DateKey",each ([Year] * 10000) + ([Month] * 100) + [Day]),
    FullDateNameAdded = Table.AddColumn(DateKeyAdded,"DateFullName",each DateTime.ToText(DateTime.From([Date]),"dd MMMM yyyy")),
    // Calendar Quarter 季度
    CalendarQuarterAdded = Table.AddColumn(FullDateNameAdded, "Calendar Quarter",
          each Number.IntegerDivide(Date.Month([Date])-1,3) + 1
          ),
    // Calendar Month Number 年月
    CalendarMonthNumberAdded = Table.AddColumn(CalendarQuarterAdded, "Calendar Month Number",
          each Date.Year([Date]) * 100 + Date.Month([Date])
          ),      
    // Is Week Day 工作日 or 周末
    WeekDayAdded = Table.AddColumn(CalendarMonthNumberAdded, "IsWeekDay",
          each
              if 
                   Date.DayOfWeek(DateTime.From([Date]))=Day.Sunday
                   or 
                   Date.DayOfWeek(DateTime.From([Date]))=Day.Saturday
             then 0 else 1 ),
    // Day Of Week 
    DayOfWeek = Table.AddColumn(WeekDayAdded,"DayOfWeek",each Date.DayOfWeek(DateTime.From([Date]))),
    // Month Name
    MonthName = Table.AddColumn(DayOfWeek,"Month Name",each DateTime.ToText(DateTime.From([Date]),"MMMM")),
    // Day of Week Name
    DayOfWeekName = Table.AddColumn(MonthName,"Day of Week Name",each DateTime.ToText(DateTime.From([Date]),"dddd")),
    
    // Fiscal Year 财年
    FiscalYearAdded = Table.AddColumn(DayOfWeekName,
        "Fiscal Year",
         each 
              if Date.Month([Date]) >= FinancialYearStartingMonth then 
                    Date.Year([Date]) + 1 
              else 
                    Date.Year([Date])
         ),
    // Fiscal Month 财年月份
    FiscalQuarterAdded = Table.AddColumn(FiscalYearAdded,
        "Fiscal Quarter",
         each 
              if Date.Month([Date])>=FinancialYearStartingMonth then 
                   Number.IntegerDivide((Date.Month([Date])-FinancialYearStartingMonth),3) + 1
              else
                   Number.IntegerDivide((12 + Date.Month([Date])-FinancialYearStartingMonth),3) + 1
          ),
    
    #"Changed Type" = Table.TransformColumnTypes(FiscalQuarterAdded,{{"DateFullName", type text}, {"DateKey", Int64.Type}, {"Calendar Quarter", Int64.Type}, {"Calendar Month Number", Int64.Type}, {"IsWeekDay", type logical}, {"DayOfWeek", Int64.Type}, {"Month Name", type text}, {"Day of Week Name", type text}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Date", Date.Type}})
in
    #"Changed Type"