Power BI Desktopのクイックメジャーで生成したDAX使用移動平均集計式を紐解く #PowerBI

クイックメジャーで移動平均式生成

NHKのオープンデータを使います。
データは、新型コロナウイルスの県別感染者数推移です。
「Power BI Desktop」の「クイックメジャー」で自動生成された「7日間移動平均」の式の内容を調べてみます。
クイックメジャーはインポートした値（列）を使った計算式を作り、元のデータにはない集計値を作る機能です（関数を使った新しい「列」を作るようなものですが、正確には「列」とは呼ばないようです）。

調べやすいように特定の地域（今回は「北海道」のみ」）を抽出したテーブル「ref_daily_data2」を使います。

移動平均の式を「クイックメジャー」で用意します。

生成されたメジャーのコードが以下です。

positive_cases_移動平均2 = 
IF(
    ISFILTERED('ref_daily_data2'[日付]),
    ERROR("タイム インテリジェンスのクイック メジャーは、Power BI が指定する日付階層またはプライマリの日付列でのみグループ化またはフィルターできます。"),
    VAR __LAST_DATE = LASTDATE('ref_daily_data2'[日付].[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'ref_daily_data2'[日付].[Date],
                DATEADD(__LAST_DATE, -6, DAY),
                __LAST_DATE
            ),
            CALCULATE(SUM('ref_daily_data2'[positive_cases]))
        )
)

7日間移動平均のクイックメジャーを使い、時系列折れ線グラフを作成しました。

クイックメジャーで作った移動平均の式、使われたDAX関数の中身を分解していきます。

「VAR」と「RETURN」で変数設定

「VAR」と「RETURN」の間に変数を指定しています。
RETURNで指定した列を変数として出力します。
この場合「'ref_daily_data2'[日付]」列を設定することで、列内の各値が変数として設定でき、その変数を使った計算式に組み込まれていきます。

「__LASTDATE2」は「LASTDATE」関数により、日付列の最終日付を返します。
'ref_daily_data2'[日付].[Date］の「Date」は、日付階層の中の「日にち」を指定しています。
日付型の列は、自動的に「年」「四半期」「月」「日」の階層を持ちます。
（.[Date]を付与しなくても動くケースもあるようですが、ここは後日確認するつもりです）

test_var2 = 
    VAR __LAST_DATE2 = LASTDATE('ref_daily_data2'[日付].[Date])
    RETURN
    __LAST_DATE2

以下画面のように、「年月」形式にすると。「__LASTDATE2」がその月の最終日を計算結果として返しています。

AVERAGEXで平均値算出

「AVERAGEX」関数を使う際、実行対象のテーブル（列）をまず指定します。
その列に対し、どういう条件や範囲で平均値を集計するかを設定できる関数です。
「X」が付かない「AVERAGE」は、通常列のすべての数値の平均値を返します。

以下の式で、

positive_casesの合計（SUM）を求め、
その合計値をref_daily_data2'[日付]を期間とし
期間平均値（AVERAGEX）を集計し
「test_averagex2 」に戻します。

test_averagex2 = 
AVERAGEX(
    VALUES('ref_daily_data2'[日付]),
    CALCULATE(SUM('ref_daily_data2'[positive_cases]))
)

「VALUES」は、入力パラメーターが列名の場合は、指定された列の個別の値を返します。
重複する値は削除され、一意の値のみが返されます
「VALUES」を使わず、「'ref_daily_data2'[日付]」だけの指定では値持つ列として認識されなかったようで、エラーが出ました。
「VALUES('ref_daily_data2'[日付]」とすることで、日付列の値を持つ列として扱えます（なんとなくおまじないのようにも思える便利関数と思い使いました）。

その結果、例えば月単位の表を作成すると、「positive_cases」の月平均を集計できることが確認できました（表の5列目）。
（2020年1月は16日からのデータなので、約半月の平均値となっています）

「DATESBETWEEN」で計算対象期間を指定

「DATESBETWEEN」は、指定日から指定された終了日まで日付の列が含まれるテーブルを返す関数です。
ザクっと言うと、「いつからいつまで」を指定できます。

元のデータは都道府県別日集計されているため、同じ日付の値が都道府県の数だけ存在します。
重複した値があると（自分の技量では）「DATESBETWEEN」を説明しにくいので、わかりやすいように「北海道」に絞ったの日毎の集計値（positive_cases：陽性者数）のテーブル「ref_daily_data2」を使います。

動作例として、「2021/8/25から2021/8/31までの期間のpositive_casesの集計値」を計算するメジャーを作りました。
併せて試用した「CALCULATE」関数は、以下のような動作を指定しています。

まず「SUM」で合計値を計算
その計算値を「 DATESBETWEEN」の条件でフィルタリング（「 DATESBETWEEN」で指定された期間の合計値を計算する、ということです）

test_DATESBETWEEN = 
VAR StartDate = DATE ( 2021, 08, 25 )
VAR EndDate =   DATE ( 2021, 08, 31 )
RETURN
 CALCULATE (
    SUM('ref_daily_data2'[positive_cases]), DATESBETWEEN ('ref_daily_data2'[日付].[Date], StartDate2, EndDate2))

以下のメジャーを視覚化パネルのテーブルに割り当てると「2021/8/25から2021/8/31までの期間のpositive_casesの集計値」は「2841」となります（上の表）。
これを「日付」「positive_cases」「都道府県名」のテーブルで視覚化し、「2021/8/25から2021/8/31」と期間を設定すると、その集計値も「2841」となり（下の表）、テスト用に作ったメジャー「test_DATESBETWEEN」と同じ値になります。

「DATEADD」で集計期間を固定

今回扱っているのは「7日間移動平均」なので、日にちが進んでも「常に集計日+その前の6日間」の計７日間に固定しなければいけません。
「 DATESBETWEEN」と「DATEADD」関数を組み合わせて指定しています。

まず「DATEADD」の動作について。
指定された間隔を前後にシフトした日付の列を含むテーブルを返します。
例えば、「positive_cases」の平均値を「AVERAGE」関数で求めます。
「日付」テーブルは「年月」表示にし、月平均値を集計します。

Average = 
CALCULATE(AVERAGE('ref_daily_data2'[positive_cases]))

「DATEADD」を使い、集計対象月に一カ月前の平均値を計算するメジャーを作ります。
「DATEADD」の条件として「,-1,MONTH」と指定することで、「前月分を集計する」という設定が組み込まれています。

上記で設定したへ金地を求めるメジャー「AVERAGE」計算式に指定
その「AVERAGE」を「DATEADD」でフィルタリング（今回の場合、集計月にその月でなく、「DATEADD」で指定した時間の平均値を算出する、という振る舞いになります）

Average_test_dateadd = 
CALCULATE(
    [Average],
    DATEADD('ref_daily_data2'[日付],-1,MONTH)
    )

「年」「月」「Average」「Average_test_dateadd」という表を作ると、当月平均値の隣にその前月の平均値が並ぶことが確認できます。

各関数を組み合わせる

これらを踏まえ、7日間移動平均で使われている部分の動きを確認するには、先述の通り「集計日とその前の6日間の（計）7日間の平均を求める」ということになります。
「VAR」「 DATESBETWEEN」「DATEADD」「AVERAGEX」を組み合わせます。

集計日を指定します。

    VAR __LAST_DATE = LASTDATE('daily_data'[日付].[Date])
    RETURN

集計日の前の6日間「DATEADD( __LAST_DATE, -6, DAY)」から集計日「__LAST_DATE」までを指定。この設定を一連の処理に組み合わせます。
「DATEDD」は、指定された間隔を前後にシフトした日付の列を含むテーブルを返します（例えば何日前、何カ月前の日付を集計できる）。

            DATESBETWEEN(
                'daily_data'[日付].[Date],
                DATEADD(__LAST_DATE, -6, DAY),
                __LAST_DATE 
            )

●上記7日間の平均値を求めます。
●「AVERAGEX」の後にフィルタするテーブル「DATESBETWEEN(～)」を指定し、そのテーブルの範囲で、指定した計算式「SUM(～)」の平均値を計算。この設定を組み合わせます。

            AVERAGEX(
                DATESBETWEEN(
                    'daily_data'[日付].[Date],
                    DATEADD(__LAST_DATE, -6, DAY),
                    __LAST_DATE
                ),
                CALCULATE(SUM('daily_data'[positive_cases]))

これで冒頭に紹介した7日間移動平均の式ができました。

ISFILTEREDは（たぶん）エラーメッセージ用

クイックメジャーにより生成された式には以下の「IF文」が付与されています。
「ISFILTERED」は、引数を列名とし、その列に直接フィルターが適用されている場合「TRUE」を返します。
これが何のためにあるのか、自分のスキルではよくわかりませんでした。
ただ、以下のように「--」でコメントアウトしても異動平均値は計算されました。

positive_cases_移動平均2-2 = 
--IF(
--    ISFILTERED('ref_daily_data2'[日付]),
--    ERROR("タイム インテリジェンスのクイック メジャーは、Power BI が指定する日付階層またはプライマリの日付列でのみグループ化またはフィルターできます。"),
    VAR __LAST_DATE = LASTDATE('ref_daily_data2'[日付].[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'ref_daily_data2'[日付].[Date],
                DATEADD(__LAST_DATE, -6, DAY),
                __LAST_DATE
            ),
            CALCULATE(SUM('ref_daily_data2'[positive_cases]))
        )
--)

上記の「positive_cases_移動平均2-2」と元々の「positive_cases_移動平均2 」は一致しています。

では、どういうときに「FALSE」となり、指定されたエラーメッセージが出るかを試してみました。
「日付」フィールド（列）を右クリックし、「日付の階層」から「日付」に変更します。

すると、先ほどのグラフが以下のようにエラーとなります。

「詳細を確認する」をクイックすると、エラーメッセージが返されました。
この場合、日付型データが日にちだけでなく「年」「四半期」「月」「日」と階層化され、それぞれの単位でフィルタリング（集計）できるようになってないとと、移動平均のメジャーが使えない、ということをアラートとして出すために記載されているようです。

「日付」から「日付の階層」に戻すと、グラフが元に戻ります。

最初は年単位の集計です

「ドリルモード」を「オン」にします。

「ドリルダウン」すると、日単位の集計に戻ります。

了