PowerBI DAX 數(shù)據(jù)建模處理中國節(jié)假日

在日期維度進(jìn)行計算時，經(jīng)常會涉及到節(jié)假日問題，本文給出一個參考。

• 對于任何一個日期，我們希望識別并知道該日期是否是工作日還是一個非工作日；

• 如果是非工作日，還希望進(jìn)一步知道是周末還是法定的節(jié)假日；

• 如果是工作日，還希望進(jìn)一步知道是普通日還是由于節(jié)假日導(dǎo)致的調(diào)換。

例如：

用工作日來標(biāo)識工作日或非工作日，以及進(jìn)一步的劃分。

作為日期的屬性

與工作日以及節(jié)假日有關(guān)的信息可以認(rèn)為是日期的一種屬性，就是：

• 工作日類型枚舉：工作日，非工作日。

• 節(jié)日類別枚舉：中國法定節(jié)假日，普通，周末，補(bǔ)工作日，補(bǔ)休，自定義節(jié)假日。

• 節(jié)日名稱：元旦節(jié)，春節(jié)，清明節(jié)，勞動節(jié)，端午節(jié)，中秋節(jié)，國慶節(jié)。

這個劃分的動機(jī)就是為了區(qū)分工作日以及非工作日以及它們的來源。

同樣對于同一個國慶節(jié)，它既可能是節(jié)日的來源，也可能是工作日的來源（補(bǔ)工作日）。

數(shù)據(jù)模型

對于某個日期，要么工作，要么休假，其最終狀態(tài)不可能既是工作日又是節(jié)假日。根據(jù)這個特點(diǎn)，如果存在一個假日表，那么其日期應(yīng)該是非重復(fù)的。那么，一個普通的日期表，最多只能從假日表中找到一項與之匹配，也就是一對一的關(guān)系。

先來看下假日表的結(jié)構(gòu)：

假日表應(yīng)該事先準(zhǔn)備好，其信息很容易在網(wǎng)絡(luò)獲悉，這里面僅僅需要記錄特殊的假日以及調(diào)整即可。

在數(shù)據(jù)模型的設(shè)計上，的確可以將假日表與日期表建立關(guān)聯(lián)，數(shù)據(jù)模型會更加復(fù)雜，在數(shù)據(jù)模型中已經(jīng)有一個日期表，而節(jié)假日實際是日期的一種屬性，為了保持日期表的簡單統(tǒng)一，這里考慮在構(gòu)建日期表的時候，直接擴(kuò)展三列作為日期的一種屬性存在。

通用日期表

通用日期表的構(gòu)建，可以參考此前的文章，這里給出一個簡單的版本，用 Power BI DAX 構(gòu)建如下：

// 構(gòu)建通用的日期表
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

為了支持節(jié)假日，可以在 RETURN 之前先做一定的擴(kuò)展。

準(zhǔn)備節(jié)假日表

在進(jìn)行擴(kuò)展前，節(jié)假日表應(yīng)該預(yù)先構(gòu)建好，例如以 Excel 的形式存在。如下：

將節(jié)假日表加載到 Power BI 中后再對上述的 Calendar 進(jìn)行擴(kuò)展。

擴(kuò)展日期表支持節(jié)假日

按照上述思路，在返回帶節(jié)假日屬性的日期表之前做一個擴(kuò)展，用 Power BI DAX 實現(xiàn)如下：

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 )
    )
// 擴(kuò)展
VAR date_table_result  =
    GENERATEALL( 
        date_table_base , 
        VAR _workday_type   = LOOKUPVALUE( Holiday[工作日類型] , Holiday[日期] , [Date] )
        VAR _holiday_name   = LOOKUPVALUE( Holiday[節(jié)日名稱] , Holiday[日期] , [Date] ) 
        VAR _workday_change = LOOKUPVALUE( Holiday[節(jié)日增補(bǔ)] , 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 函數(shù)來完成這一任務(wù)，其核心邏輯是：

• 迭代?date_table_base?中的每行，

• 在其行上下文中計算

• 創(chuàng)建一個單行的表并與該行做叉積（左表的每行都對應(yīng)右表的所有行）運(yùn)算

注意：

• LOOKUPVALUE?用來查找值

• ROW?用來構(gòu)建集合

之所以沒有用?ADDCOLUMNS?而是改用?GENERATEALL?是考慮到重用。

最終得到了包括節(jié)假日屬性的日期表。

總結(jié)

按照操作，得到結(jié)果如下：

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

• 更準(zhǔn)確的計算日期之間的工作日天數(shù)

• 更準(zhǔn)確的計算在工作日實現(xiàn)的交易額，假日的交易額

• …

那更深入的問題又來了，如果有很多活動，雖然不是節(jié)假日，但也是特殊的日期，怎么處理？

可以參考節(jié)假日的思路，再準(zhǔn)備一個活動日歷表，包括：店慶日，雙十一等。

大家可以自己嘗試，后續(xù)文章和大家進(jìn)一步探討。

在訂閱了BI佐羅講授的《BI進(jìn)行時》課程區(qū)，除了可以下載本文案例，還可以觀看視頻講解。

讓數(shù)據(jù)真正成為你的力量

Create value?through?simple and?easy?with fun?by PowerBI

Excel BI?|?DAX Pro?|?DAX?權(quán)威指南?|?線下VIP學(xué)習(xí)

掃碼與PBI精英一起學(xué)習(xí)，驗證碼：data2020

PowerBI MVP 帶你正確而高效地學(xué)習(xí) PowerBI
點(diǎn)擊“閱讀原文”，即刻開始

↙