今回はカレンダーテーブルを活用して、時系列に関わる計算を行っていきます

前回でカレンダーテーブルを活用する準備が完全に整った状態です

数字を語る時にかならず「比較」が必要となりますが、この比較を自動的に行えるようにしましょう！

今回から「メジャー」という仕組み使用します

このメジャーの仕組みはエクセルのワークシートにはない仕組みです

メジャーはテーブルでもなく列でもなく、？の箇所の数字を埋めてくれる仕組みです

あくまで前後の文脈が決まってから数字が計算されます

例えば、「今年の４月」の数字があったとしたら、前年の４月の数字を、「２０２１年」の数字があったとしたら「２０２０年」の数字を自動的に穴埋めする仕組みです

このメジャーをつかいこなせば、レポート作業は格段に効率化されます

前年の値を計算するメジャーを作成する

まずはメジャーをクリックしてみましょう！

すると、下の画像のように数式を入力できるようになります

ここからはカレンダーテーブルを作成した時の要領で行えます

前年 = CALCULATE(SUM(‘売上データ'[売上金額]),DATEADD(‘Calendar'[Date],-1,YEAR))

これで、前年のメジャーが作成されました

このメジャーをマトリックスの「値」に配置してみます

すると、マトリックスの時系列の表示に合わせ、前年の値が埋められて表示されます

この前年の値の式はCALCULATE関数を基にして計算されます

前年 = CALCULATE(SUM(‘売上データ'[売上金額]),DATEADD(‘Calendar'[Date],-1,YEAR))

CALCULATE関数内では、SUM関数が使用されていますが、このSUM関数で売上金額の合計金額が算出されます

但し、売上金額を算出する際に「DATEADD関数」でフィルター処理が行われる仕組みです

ちなみにDATEADD関数の真ん中に「ー１」が指定されていますが、こちらをー２に指定すると前々年の数字が算出されます

仮にDATEADD関数の真ん中「ー１」のままで「YEAR」を「MONTH」に置き換えると前月の数字が算出されます

累計の値を算出するメジャーを作成する

今度は累計を算出するメジャーをTOTALYTD関数を使用して作成してみましょう

計算式は次の通りとなります

累計 = TOTALYTD(SUM(‘売上データ'[売上金額]),’Calendar'[Date])

実際にマトリックスに配置してみると、確かに月ごとの加算は行われています

但し、４月の累計値は４月の数字で開始されていません

これは、累計処理が１～１２月を会計年度として処理されているからです

こちらを４月～翌年３月の仕様に変えてみます

こちらは、累計処理の終了基準を「3/31」で明確に示しています

これで累計処理が正しく行われます

前年比などの指標

前年比などの指標はクイックメジャーで簡単に作成することができます

クイックメジャーの中には様々な指標があらかじめ準備されています

試しに「差の割合」をクリックしてみます

すると、右のフィールドの値を左側の該当箇所にあてはめれば計算が行われるようになっています

数字を当てはめて「OK」ボタンを押せばメジャーが設定されます

新しく作成されたメジャーをマトリクスに配置すると次のようになります

「前年との相違は売上金額 %」となっていますが、こちらは変更することができます

「前年との相違は売上金額 %」を選択し、数式バーから変更します

＜まとめ＞

今回は前年の数字などの数字を算出するメジャーを作成しました

このメジャーを使用するとレポート作業の効率は格段に向上します

今回のシリーズでカレンダーテーブル活用に関する一連の必要事項を紹介しました

ぜひ、実際に手を動かしてものにしてみましょう！

最後に、「年」⇒「月」と階層を変えるドリルダウンの仕組みはとても便利ですので、こちらも有効活用しましょう

にほんブログ村