BI技巧丨近兩年及當年月份資料彙總

BOSS：白茶，有個需求，不知道你能不能做？

白茶：不能！

BOSS：我還沒說呢，小夥砸！做了加錢！

白茶：BOSS您吩咐！

BOSS：是這樣的，能不能搞個柱形圖，既展示近兩年的資料，又展示當年所有月份的資料，要求年月在一個次元上？

白茶：小意思！安排！

實際業務中，比較成熟的企業會有相應的市場團隊，根據以往的營業情況和當下的市場行情為當年制定合理的銷售目标，但是也會有一些小企業沒有相應的業務團隊，那麼這種情況下，需要如何制定銷售目标麼？

其實針對這種情況，可以将往年的曆史資料和截至到當月的資料合并進行展示，這樣可以參照往年的經驗來評估今年的整體經營狀況。

那麼在PowerBI中如何實作這一需求呢？

案例資料：

這是本期的資料，僅有兩列，一列日期，一列銷售數量。

将其導入到PowerBI，并且根據如下代碼，添加日期表，建立模型關系。

Dim_DateI = 
GENERATE (
    CALENDAR ( MIN ( 'Fact_Sales'[Date] ), MAX ( 'Fact_Sales'[Date] ) ),
    VAR DA = [Date]
    VAR YEAR =
        YEAR ( DA )
    VAR QUARTER =
        FORMAT ( DA, "Q" )
    VAR MONTE =
        FORMAT ( DA, "MM" )
    VAR DAY =
        DAY ( DA )
    VAR WEEKID =
        WEEKDAY ( DA, 2 )
    RETURN
        ROW (
            "Year", YEAR,
            "Quarter", QUARTER,
            "YearMonth",
                YEAR & "." & MONTE,
            "Day", DAY
        )
)
           

到這裡，準備工作完成了，我們來分析一下思路。

要實作年月同軸展示，那麼需要有一張展示使用的次元表，可以将年月合并在一列中，根據如下代碼建構第二張日期表。

Dim_DateII = 
VAR YearDate =
    SELECTCOLUMNS (
        ADDCOLUMNS ( VALUES ( Dim_DateI ), "Type", "Year" ),
        "Type", [Type],
        "Year", [Year],
        "Display", [Year],
        "YearMonth", [YearMonth],
        "Date", [Date]
    )
VAR MonthDate =
    SELECTCOLUMNS (
        ADDCOLUMNS ( VALUES ( Dim_DateI ), "Type", "Month" ),
        "Type", [Type],
        "Year", [Year],
        "Display", [YearMonth],
        "YearMonth", [YearMonth],
        "Date", [Date]
    )
RETURN
    UNION ( YearDate, MonthDate )
           

結果如下：

可以看到，年月已經在同一字段中了。

模型關系如下：

這裡需要注意一下，第二張日期表不需要和之前的表建立模型關系。

編寫如下代碼：

銷售數量：

001.Quantity = 
SUM ( Fact_Sales[Quantity] )
           

切片器日期：

002.CurrentDate = 
MAX ( 'Dim_DateI'[Date] )
           

切片器年份：

003.CurrentYear = 
MAX ( 'Dim_DateI'[Year] )
           

PS：切片器的兩個路徑成本，是為了截斷時間和計算使用。

軸類型：

004.Dim_Type = 
MAX ( 'Dim_DateII'[Type] )
           

展示銷售數量：

005.CurrentQuantity = 
VAR DimDate =
    VALUES ( Dim_DateII[Date] )
VAR Result =
    CALCULATE (
        [001.Quantity],
        FILTER ( ALL('Dim_DateI'), 'Dim_DateI'[Date] IN DimDate )
    )
RETURN
    Result
           

結果如下：

從結果上我們可以看到，已經将原本的銷售數量，在新日期表下面關聯起來了。

繼續編寫如下代碼：

006.ResultQuantity = 
IF (
    [004.Dim_Type] = "Year",
    IF (
        MAX ( 'Dim_DateII'[Year] ) <= [003.CurrentYear]
            && MAX ( 'Dim_DateII'[Year] ) >= [003.CurrentYear] - 2,
        IF (
            MAX ( 'Dim_DateII'[Year] ) < [003.CurrentYear],
            [005.CurrentQuantity],
            CALCULATE (
                [005.CurrentQuantity],
                FILTER (
                    ALL ( Dim_DateII ),
                    [Year] = [003.CurrentYear]
                        && [Date] <= [002.CurrentDate]
                )
            )
        )
    ),
    IF (
        MAX ( 'Dim_DateII'[Date] ) <= [002.CurrentDate]
            && MAX ( 'Dim_DateII'[Year] ) = [003.CurrentYear],
        [005.CurrentQuantity],
        BLANK ()
    )
)
           

最終展示效果如下：

PS：

1.年月想要同時展示，建構一張新表是必要條件；

2.如何将新表和原本模型關系中的日期表關聯起來，是問題的核心點，可以使用TREATAS函數，也可以使用IN或者INTERSECT函數，方式不唯一；

3.結果展示上，注意多層IF判斷，思路要清晰；

4.也可以展示多個年份資料，調整内部參數即可。

這裡是白茶，一個PowerBI的初學者。