Power BIのクイックメジャーで累計を算出

概要

• クイックメジャーで初心者でも累計を計算する方法
• クイックメジャーの落とし穴と対処法を備忘録的にまとめてみます。

例題

ある会計年度の年月による売上累計を作成する場合を例にします。
年度が替わるとリセットされる累計です。

事前にカレンダーテーブルを作成し、自由な日付軸、今回は年月単位で可視化できるようにします。

特定の会計年度だけ表示できるよう、スライサーも配置しておきます。

クイックメジャーを使った累計

TOTALYTDによる方法

年度累計を使用します。
以下画像のように設定

出力されるDAX

売上 YTD = 
IF(
    ISFILTERED('カレンダー'[Date]),
    ERROR("タイム インテリジェンスのクイック メジャーは、Power BI が指定する日付階層またはプライマリの日付列でのみグループ化またはフィルターできます。"),
    TOTALYTD(SUM('Main'[売上]), 'カレンダー'[Date].[Date])
)

落とし穴と対応

①基本的にうまくいかない。

②'カレンダー'[Date].[Date]の.[Date]がいらないので消す

③すると、フィルターした年度の累計が表示される
ただし既定では1月始まりの12/31でリセットされる累計になります。

④そこで、第三引数に3/31を年度末とするよう指定
※ドキュメントによると第三引数はフィルター関数だけど第四引数の値を入力すると自動的に省略して認識

売上 YTD = 
IF(
    ISFILTERED('カレンダー'[Date]),
    ERROR("タイム インテリジェンスのクイック メジャーは、Power BI が指定する日付階層またはプライマリの日付列でのみグループ化またはフィルターできます。"),
    TOTALYTD(SUM('Main'[売上]), 'カレンダー'[Date],"3/31")
)

⑤これで3/31リセットの売上が計算できました。

「累計」使った方法

クイックメジャーの「累計」を使用します。
以下画像のように設定します。

出力されるDAX
IS ON OR AFTER関数になります。
ISONORAFTERってつなげられると訳が分からないですね。

Date の 売上 に対する累積値 = 
CALCULATE(
    SUM('Main'[売上]),
    FILTER(
        ALLSELECTED('カレンダー'[Date]),
        ISONORAFTER('カレンダー'[Date], MAX('カレンダー'[Date]), DESC)
    )
)

落とし穴と対応

①フィルターした年度内ではなく当初からの累計になる
ALLSELECTED関数により、会計年度のフィルターが適用されるはずなのですが、
過去全てからの累計になっています。
ALLSELECTEDの中身をよく見ると、[Date]列限定になっていますね。

なのでDate列でフィルターをセットしたときだけフィルターが適用され年度初めからの累計になります。

②ALLSELECTED関数の[Date]を消し他の列のフィルターも適用されるようにする。
これで会計年度でフィルターした場合でもちゃんと動作します。

Date の 売上 に対する累積値 = 
CALCULATE(
    SUM('Main'[売上]),
    FILTER(
        ALLSELECTED('カレンダー'),
        ISONORAFTER('カレンダー'[Date], MAX('カレンダー'[Date]), DESC)
    )
)

③年度フィルターがない場合でも年度単位でリセットされるようにするには

AND条件で以下のように同じ年度にフィルターします。

CALCULATE(
    SUM('Main'[売上]),
    FILTER(
        ALLSELECTED('カレンダー'),
        ISONORAFTER('カレンダー'[Date], MAX('カレンダー'[Date]), DESC) &&
        'カレンダー'[会計年度] = SELECTEDVALUE('カレンダー'[会計年度])
    ) 
)

SELECTEDVALUEは、各行コンテキスト(この場合年月)の中で、引数に指定されたカテゴリが一種類の場合にその値を単一値(スカラー値)として返すようです。
例えば'20/4の行には年度は2020の一つしかないため2020が返され、2020年度のみにフィルターして計算します。

DAX数式を使った累計

DAXに慣れてきたらこちらで。
ISONORAFTERは初めての人が見ると？？？ってなるのでこちらの方が分かりやすいかもしれません。

売上累計_会計年度 = 
CALCULATE(
    SUM('Main'[売上]),
    FILTER(
        ALLSELECTED('カレンダー'),
        //現在行の最大日付以下にフィルター
        'カレンダー'[Date] <= MAX('カレンダー'[Date]) &&
        //現在行と同じ会計年度にフィルター
        'カレンダー'[会計年度] = SELECTEDVALUE('カレンダー'[会計年度])
    )
)

PowerBIは一回作ると暫くメンテナンスが必要ないため、DAXを忘れてしまいます。
コメントを書いておくと思い出しやすいです。