新DAX関数、WINDOWを使ってみました

Power BI で2022年12月から使える新DAX関数のうち、WINDOWをつかってみました。

集計した「テーブル」や「マトリックス」の中で、出力された計算結果のデータから
相対位置や絶対位置のデータを指定して計算することができます。

WINDOW (1,ABS,0,REL,・・・）と計算すると累計が得られます。

今回紹介したデータのPower BI ファイルをここに保存しています。
よろしければダウンロードしてご自由に触ってみてください。

ダウンロードはこちら　⇒　リンク先はこちらをクリック

算出したい事

１．WINDOW関数を使って、販売した商品の累積返品率を算出したい。

２．販売開始から返品受付の終了までの間とします。
　　それ以外のデータは省きたい。

３．X軸が「年月」から「販売開始からの経過月」に
　　変更する方法について説明したいと思います。

算出したい最終形：Divide

販売した商品の販売数と返品数の累計値をWINDOW関数を使って算出し、
それぞれの累計値を用いて、累積返品率(Divide)を算出します。

累計がうまくいかない例

WINDOW関数を使わなかった場合
１．SalesやReturnテーブルにデータが存在しない日は累計値が出ない

販売・返品のもとデータに存在する日だけ累計値がでる

累計販売数_日付 =
VAR _current_sales_day =
    MAX ( 'F_販売_T'[販売日] )
RETURN
    CALCULATE ( [販売数_合計], 'D_Dates'[Date] <= _current_sales_day )

２．日付テーブルの最大日まで累計すると、
　　SalesやReturnデータが存在しない日も累計値が出てしまう

先程、累計をしても空欄になってしまう年月があった。
空欄を埋めるために、「販売データの最大日まで」➡「日付データの最大日まで」に
変更すると、累計値の空欄は埋まります。
しかし、必要ない年月まで空欄が埋まってしまいます。

販売・返品のもとデータに存在しない日も累計値がでる　(DatesテーブルのMAX日まででる)

累計販売数_日付 =
VAR _current_sales_day =
    MAX ( 'Dates'[Date] )
RETURN
    CALCULATE ( [販売数_合計], 'D_Dates'[Date] <= _current_sales_day )

算出したい区間の、判定条件を決めて解決した

販売と返品データを合わせて、全体のMinとMaxの日付を算出

次の表を作成した。

構文は次の内容です。
　各商品の販売開始日から、最小年月(Min_YM)
　販売と返品の最大日から、最大年月(MAX_YM)を求めています。

最大経過月_製品毎

D_経過月_T_製品毎 =
ADDCOLUMNS (
    GROUPBY (
        'D_販売情報',
        'D_販売情報'[販売_モデル],
        'D_販売情報'[販売開始日],
        'D_販売情報'[MAX販売日],
        'D_返品情報'[MAX修理日],
        'D_販売情報'[MAX経過月],
        'D_返品情報'[MAX経過月_修理]
        ),
    "Max_Day",
        IF (
            'D_販売情報'[MAX販売日] >= 'D_返品情報'[MAX修理日],
            'D_販売情報'[MAX販売日],
            'D_返品情報'[MAX修理日]
        ),
    "Min_YM", FORMAT ( 'D_販売情報'[販売開始日], "yymm" ),
    "Max_YM",
        IF (
            'D_販売情報'[MAX販売日] >= 'D_返品情報'[MAX修理日],
            FORMAT ( 'D_販売情報'[MAX販売日], "yymm" ),
            FORMAT ( 'D_返品情報'[MAX修理日], "yymm" )
        ),
    "Max_Month",
        IF (
            'D_販売情報'[MAX経過月] >= 'D_返品情報'[MAX経過月_修理],
            'D_販売情報'[MAX経過月],
            'D_返品情報'[MAX経過月_修理]
        )
)

判定=1とする期間は、最小年月(Min_YM)から最大年月(MAX_YM)まで
構文はこちら。

判定_年月

判定_年月 = 
IF (
    CALCULATE (
        ( MIN ( 'D_経過月_T_製品毎'[Min_YM] ) <= MAX ( 'D_YM_年月'[年月] ) )
     && ( MAX ( 'D_YM_年月'[年月] ) <= MAX ( 'D_経過月_T_製品毎'[Max_YM] ) )
    ),
    1,""
)

イメージはこちら。
最小年月(Min_YM)と最大年月(MAX_YM)が、背景オレンジ色のデータです。

WINDOW関数で累積計算します。

販売数_累計_年月

販売数_累計_年月 =
IF (
    [判定_年月] = 1,
    CALCULATE (
        [販売数_合計],
        WINDOW (
            1,ABS,
            0,REL,
            ALLSELECTED ( 'D_YM_年月'[年月] ),
            ORDERBY ( 'D_YM_年月'[年月] ),
            KEEP
        )
    )
)

集計結果

販売開始からの経過月

今までは、X軸＝横軸が「年月」でしたが
「販売開始からの経過月」に変更する作成手順を示します。

判定_経過月

判定_経過月 =
IF ( [経過月合計] <= CALCULATE ( SUM ( 'D_経過月_T_製品毎'[Max_Month] ) ), 1, "" )

販売数_累計_経過月

販売数_累計_経過月 =
IF (
    [判定_経過月] = 1,
    CALCULATE (
        [販売数_合計],
        WINDOW (
            1,ABS,
            0,REL,
            ALLSELECTED ( 'D_経過月_T'[経過月] ),
            ORDERBY ( 'D_経過月_T'[経過月] ),
            KEEP
        )
    )
)

モデル