PowerBI DAX 数据建模处理中国节假日-技术圈

在日期维度进行计算时，经常会涉及到节假日问题，本文给出一个参考。

对于任何一个日期，我们希望识别并知道该日期是否是工作日还是一个非工作日；
如果是非工作日，还希望进一步知道是周末还是法定的节假日；
如果是工作日，还希望进一步知道是普通日还是由于节假日导致的调换。

例如：

用工作日来标识工作日或非工作日，以及进一步的划分。

作为日期的属性

与工作日以及节假日有关的信息可以认为是日期的一种属性，就是：

工作日类型枚举：工作日，非工作日。
节日类别枚举：中国法定节假日，普通，周末，补工作日，补休，自定义节假日。
节日名称：元旦节，春节，清明节，劳动节，端午节，中秋节，国庆节。

这个划分的动机就是为了区分工作日以及非工作日以及它们的来源。

同样对于同一个国庆节，它既可能是节日的来源，也可能是工作日的来源（补工作日）。

数据模型

对于某个日期，要么工作，要么休假，其最终状态不可能既是工作日又是节假日。根据这个特点，如果存在一个假日表，那么其日期应该是非重复的。那么，一个普通的日期表，最多只能从假日表中找到一项与之匹配，也就是一对一的关系。

先来看下假日表的结构：

假日表应该事先准备好，其信息很容易在网络获悉，这里面仅仅需要记录特殊的假日以及调整即可。

在数据模型的设计上，的确可以将假日表与日期表建立关联，数据模型会更加复杂，在数据模型中已经有一个日期表，而节假日实际是日期的一种属性，为了保持日期表的简单统一，这里考虑在构建日期表的时候，直接扩展三列作为日期的一种属性存在。

通用日期表

通用日期表的构建，可以参考此前的文章，这里给出一个简单的版本，用 Power BI DAX 构建如下：

// 构建通用的日期表
Calendar = 

VAR d1 = MINX( { MIN( 'Order'[OrderDate] ) /* MIN( T2[Date] ), ... , MIN( Tn[Date] ) */ } , [Value] ) 
VAR d2 = MAXX( { MAX( 'Order'[OrderDate] ) /* MAX( T2[Date] ), ... , MAX( Tn[Date] ) */ } , [Value] ) 
VAR dates = CALENDAR( DATE( YEAR( d1 ) , 1 , 1 ) , DATE( YEAR( d2 ) , 12 , 31 ) )
VAR date_table_base =
    ADDCOLUMNS( dates ,
        "YearNumber" , YEAR( [Date] ),
        "YearName" , "Y" & YEAR( [Date] ),
        "MonthNumber" , MONTH( [Date] ),
        "MonthName" , FORMAT( [Date] , "mmm" ),
        "YearMonthNumber" , YEAR( [Date] ) * 100 + MONTH( [Date] ),
        "YearMonthName" , "Y" & YEAR( [Date] ) & FORMAT( MONTH( [Date] ) , "00" ),
        "DayInWeek" , FORMAT( [Date] , "aaa" ),
        "DayNumberInWeek" , WEEKDAY( [Date] , 2 )
    )
ERTURN date_table_base

为了支持节假日，可以在 RETURN 之前先做一定的扩展。

准备节假日表

在进行扩展前，节假日表应该预先构建好，例如以 Excel 的形式存在。如下：

将节假日表加载到 Power BI 中后再对上述的 Calendar 进行扩展。

扩展日期表支持节假日

按照上述思路，在返回带节假日属性的日期表之前做一个扩展，用 Power BI DAX 实现如下：

Calendar = 

VAR d1 = MINX( { MIN( 'Order'[OrderDate] ) /* MIN( T2[Date] ), ... , MIN( Tn[Date] ) */ } , [Value] ) 
VAR d2 = MAXX( { MAX( 'Order'[OrderDate] ) /* MAX( T2[Date] ), ... , MAX( Tn[Date] ) */ } , [Value] ) 
VAR dates = CALENDAR( DATE( YEAR( d1 ) , 1 , 1 ) , DATE( YEAR( d2 ) , 12 , 31 ) )
// 基本
VAR date_table_base =
    ADDCOLUMNS( dates ,
        "YearNumber" , YEAR( [Date] ),
        "YearName" , "Y" & YEAR( [Date] ),
        "MonthNumber" , MONTH( [Date] ),
        "MonthName" , FORMAT( [Date] , "mmm" ),
        "YearMonthNumber" , YEAR( [Date] ) * 100 + MONTH( [Date] ),
        "YearMonthName" , "Y" & YEAR( [Date] ) & FORMAT( MONTH( [Date] ) , "00" ),
        "DayInWeek" , FORMAT( [Date] , "aaa" ),
        "DayNumberInWeek" , WEEKDAY( [Date] , 2 )
    )
// 扩展
VAR date_table_result  =
    GENERATEALL( 
        date_table_base , 
        VAR _workday_type   = LOOKUPVALUE( Holiday[工作日类型] , Holiday[日期] , [Date] )
        VAR _holiday_name   = LOOKUPVALUE( Holiday[节日名称] , Holiday[日期] , [Date] ) 
        VAR _workday_change = LOOKUPVALUE( Holiday[节日增补] , Holiday[日期] , [Date] )
        RETURN ROW( 
            "WorkDayType" ,  
                SWITCH( _workday_type , 
                    "工作日" , "工作日" , 
                    "非工作日" , "非工作日" , 
                    BLANK() , IF( [DayNumberInWeek] IN { 6 , 7 } , "非工作日" , "工作日" )
                ),
            "HolidayCategory" ,
                SWITCH( _workday_type ,
                    "非工作日" , _workday_change ,
                    "工作日" , _workday_change ,
                    BLANK() , IF( [DayNumberInWeek] IN { 6 , 7 } , "周末" , "普通" )
                ),
            "HolidayName" ,
                SWITCH( _workday_type ,
                    "非工作日" , _holiday_name ,
                    "工作日" , _holiday_name ,
                    BLANK() , IF( [DayNumberInWeek] IN { 6 , 7 } , "周末" , "普通" )
                )
        )
    )
RETURN date_table_result

这里使用了 GENERATEALL , LOOKUPVALUE 以及 ROW 三个主要的 DAX 函数来完成这一任务，其核心逻辑是：

迭代 date_table_base 中的每行，

在其行上下文中计算
创建一个单行的表并与该行做叉积（左表的每行都对应右表的所有行）运算

注意：

LOOKUPVALUE 用来查找值
ROW 用来构建集合

之所以没有用 ADDCOLUMNS 而是改用 GENERATEALL 是考虑到重用。

最终得到了包括节假日属性的日期表。

总结

按照操作，得到结果如下：

有了日期表的假日属性，可以做很多事情：

更准确的计算日期之间的工作日天数
更准确的计算在工作日实现的交易额，假日的交易额
…

那更深入的问题又来了，如果有很多活动，虽然不是节假日，但也是特殊的日期，怎么处理？

可以参考节假日的思路，再准备一个活动日历表，包括：店庆日，双十一等。

大家可以自己尝试，后续文章和大家进一步探讨。

在订阅了BI佐罗讲授的《BI进行时》课程区，除了可以下载本文案例，还可以观看视频讲解。

让数据真正成为你的力量

Create value through simple and easy with fun by PowerBI

Excel BI | DAX Pro | DAX 权威指南 | 线下VIP学习

扫码与PBI精英一起学习，验证码：data2020

PowerBI MVP 带你正确而高效地学习 PowerBI
点击“阅读原文”，即刻开始

↙