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

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

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

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

https://learn.microsoft.com/ja-jp/dax/window-function-dax

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

[link-1]: http://bianchi7555.main.jp/power-bi/

ダウンロードはこちら　⇒　[リンク先はこちらをクリック][link-2]

[link-2]: http://bianchi7555.main.jp/power-bi/

## 算出したい事
:::note info
１．WINDOW関数を使って、販売した商品の累積返品率を算出したい。
:::
:::note info
２．販売開始から返品受付の終了までの間とします。
　　それ以外のデータは省きたい。
:::
:::note info
３．X軸が「年月」から「販売開始からの経過月」に
　　変更する方法について説明したいと思います。
:::
## 算出したい最終形：Divide
販売した商品の販売数と返品数の累計値をWINDOW関数を使って算出し、
それぞれの累計値を用いて、累積返品率(Divide)を算出します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/c9c8faf5-bc96-bdbe-2b60-e2c7d517203f.png)
## 累計がうまくいかない例
WINDOW関数を使わなかった場合
１．SalesやReturnテーブルにデータが存在しない日は累計値が出ない
```ruby:販売・返品のもとデータに存在する日だけ累計値がでる
累計販売数_日付 =
VAR _current_sales_day =
    MAX ( 'F_販売_T'[販売日] )
RETURN
    CALCULATE ( [販売数_合計], 'D_Dates'[Date] <= _current_sales_day )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/ffb5e040-1b29-cba7-15ef-5ec377a9c291.png)

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

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

```ruby:販売・返品のもとデータに存在しない日も累計値がでる　(DatesテーブルのMAX日まででる)
累計販売数_日付 =
VAR _current_sales_day =
    MAX ( 'Dates'[Date] )
RETURN
    CALCULATE ( [販売数_合計], 'D_Dates'[Date] <= _current_sales_day )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/433e0a8d-e946-7d34-8647-a4e3efb46c20.png)
## 算出したい区間の、判定条件を決めて解決した
販売と返品データを合わせて、全体のMinとMaxの日付を算出

次の表を作成した。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/f70fcaec-3255-5ebe-4ea1-18a4f41517f4.png)
構文は次の内容です。
　各商品の販売開始日から、最小年月(Min_YM)
　販売と返品の最大日から、最大年月(MAX_YM)を求めています。
```ruby:最大経過月_製品毎
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)まで
構文はこちら。
```ruby:判定_年月
判定_年月 = 
IF (
    CALCULATE (
        ( MIN ( 'D_経過月_T_製品毎'[Min_YM] ) <= MAX ( 'D_YM_年月'[年月] ) )
     && ( MAX ( 'D_YM_年月'[年月] ) <= MAX ( 'D_経過月_T_製品毎'[Max_YM] ) )
    ),
    1,""
)
```
イメージはこちら。
最小年月(Min_YM)と最大年月(MAX_YM)が、背景オレンジ色のデータです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/05ed0db3-8141-9495-1949-5a1907427aa3.png)
WINDOW関数で累積計算します。
```ruby:販売数_累計_年月
販売数_累計_年月 =
IF (
    [判定_年月] = 1,
    CALCULATE (
        [販売数_合計],
        WINDOW (
            1,ABS,
            0,REL,
            ALLSELECTED ( 'D_YM_年月'[年月] ),
            ORDERBY ( 'D_YM_年月'[年月] ),
            KEEP
        )
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/1654d4f3-c214-81c1-400d-ee1a7bd0c3d5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/ab49623d-7a02-9275-c2d4-97af6d4a18aa.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/2389152f-b677-8f92-7e6a-0f089539230a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/2807ac06-b4f2-d5dd-1dd4-1bff695dab89.png)

## 集計結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/de984769-4ca3-6504-b4ad-94a490355fe0.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/f93db82c-62a9-e2c1-51bc-4e6d4ec5a200.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/f2ba6482-ecdd-77c8-df5a-0ac58acae1b8.png)

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

```ruby:判定_経過月
判定_経過月 =
IF ( [経過月合計] <= CALCULATE ( SUM ( 'D_経過月_T_製品毎'[Max_Month] ) ), 1, "" )
```
```ruby:販売数_累計_経過月
販売数_累計_経過月 =
IF (
    [判定_経過月] = 1,
    CALCULATE (
        [販売数_合計],
        WINDOW (
            1,ABS,
            0,REL,
            ALLSELECTED ( 'D_経過月_T'[経過月] ),
            ORDERBY ( 'D_経過月_T'[経過月] ),
            KEEP
        )
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/3d93c72a-61f9-cbb4-35aa-316e3957fdc8.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/2c6a9402-1a03-3904-651f-e38b4201e885.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/8844076d-43ba-9325-d92e-47cef139ad84.png)

## モデル
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2350540/c287f825-63da-b781-c640-563e75834d24.png)