カテゴリー別アーカイブ: 中級

前年の値や累計値を自動計算する~カレンダーテーブルを活用するツアー~

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

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

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

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

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

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

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

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

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

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

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

画像に alt 属性が指定されていません。ファイル名: image.png

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

こちらを4月~翌年3月の仕様に変えてみます

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

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

前年比などの指標

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

カレンダーテーブルの活用により、数字を時系列の軸で自由に集計するツアー

今回のシリーズ記事では、カレンダーテーブルを使用してデータを「前年データ」や「累計」などの時系列で簡単に整理し直す技術を解説したいと思います

カレンダーテーブルとは、売上データなどの日付が入ったデータをカレンダーのそれぞれの日付に割り当てて整理するためのテーブルです

カレンダーテーブル

このカレンダーテーブルが活用できるようになると、今までエクセルのワークシートで手動での転記処理に頼っていた「時系列でのデータの整理」が簡単にできるようになります

カレンダーテーブルの作成・準備

作成

ここまでの解説だと「カレンダーテーブルとはなんなのか?」が具体的にはよくわからないと思います

シンプルに言えば、まさにカレンダーのような連続した日付列を起点として作成されたデータテーブルです

この連続した日付データに年や月などを紐づけておき、売上データなどを時系列で括りなおせるようにします

リレーション

作成したカレンダーテーブルは、他テーブルとリレーション(紐づけ)を行って使用します

このリレーションを適切に行わないと、カレンダーテーブルを活用できません

会計年度の調整

日本の企業は大抵が3月決算です

ですから、1月~3月のデータは実際の年より前の年で会計年度が割り当てられます

この会計年度の調整を事前にカレンダーテーブルで行っておきます

メジャーの作成(前年比、累計など)

前年比や累計など、作成したカレンダーテーブルを活用して作成する数字はメジャーで作成します

エクセルを使用してきた感覚の延長だと、このメジャーというのはなかなか理解しがたいものです

何故なら、テーブルの列にメジャーで作成した数字はでてこないからです

これはメジャーで作成する前年比などの数字は穴埋め問題みたいなものだからです

穴埋め問題の答えは、あくまで前後の数字が決まってから答えが決まります

例えば、前年比ならば集計対象が2022年6月ということが決まってから2021年6月に対する前年比を算出することが決まります

このメジャーが穴埋め問題である点に慣れれば、後はDAXというPower BI(Power Pivot)独自の関数を使用して計算していくだけです

このDAXはワークシート関数と同じ感覚で使用できますが、文法が若干違うので注意が必要です

<まとめ>

数字を見ると時のツボは時系列で比較することです

私もこの辺の時系列のデータ整理はさんざん苦労しました

エクセルではどんなに頑張っても手動処理が入るからです

しかも、かなり気を遣うので疲れます

Power BIのカレンダーテーブルを活用して時系列でデータを自動整理する仕組みは、まさにPower BIの醍醐味といっても過言ではありません

ぜひ次回からの回でしっかりカレンダーテーブルを活用する技術を身につけましょう!

2回目:カレンダーテーブルを実際に作成する~カレンダーテーブルを活用するツアー~

3回目:カレンダーテーブルを実用的に調整する~カレンダーテーブルを活用するツアー~

4回目:前年の値や累計を使う~カレンダーテーブルを活用するツアー~

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

カレンダーテーブルを実際に作成する~カレンダーテーブルを活用するツアー~

今回は前回の内容に従って、カレンダーテーブルを実際に作成してみましょう!

カレンダーテーブルを作成する時にDAX(エクセルのワークシート関数のようなもの)も使用します

元のデータはこちらの3つのテーブルになります

ですので当初のリレーションはこのようになっています

こちらにカレンダーテーブルを組み込む形になります

新しいテーブルの作成

カレンダーテーブルの作成は最初、こちらをクリックするところから始まります

すると数式が入力できるようになります

左辺をCalendarとし、右辺にCaを入力すると、入力するDAXの候補がでてきます

こちらの候補から「CALENDAR」をTabキーで選択してください

CALENDAR()の〇括弧:()の中には、連続した日付を作成する際の開始日終了日を指定します

YEAR/年とMONTH/月の列の作成

連続した日付データができたので、今度は日付データを使用してYEARとMONTHの列を作成します

上の画像の「新しい列」をクリック後、まずはYEARの列を作成します

YEARの列を作成するにはDAX「YEAR」を指定します

その際に、コツが一つあります

丸括弧:()の中でを指定すると()中に入れる列名の候補が選択できます

今回は’Calendar[Date]の列を指定して、日付列(2021/04/01⇒2021)を年に変換して列を作成します

今、DAX:YEARをして列を作成しましたが、通常のエクセルのワークシート関数を使用した時との違いは、セルではなく列単位で関数を使用しているという点です

MONTHの列もYEARの列と同様に作成します

会計年度の調整

前回も触れましたが、3月決算の場合には会計年度を調整する必要があります

例えば、2022年1月の場合には、上記のYEAR関数で作成した列には「2022」と表示されます

ただ実際には会計年度は2022から1を引いた「2021」になります

ですので、MONTH列をもとにしてIF関数により1月~3月の会計年度を調整します

if(MONTH([Date])<4,YEAR([Date])-1,YEAR([Date]))

ちなみに四半期の列を作成するQUARTER関数を使用すると次のようになります

4月が第2QUARTERとなります

これは12月決算をもとにしてDAXが作成されているからです

この場合も3月決算にあわせてIF関数を使用します

IF(MONTH([Date])<4,4,if(MONTH([Date])<7,1,IF(MONTH([Date])<10,2,3))

リレーションシップの作成

カレンダーテーブルをひとまず、形にはしました

これだけでは、カレンダーテーブルを活用することはできません

他のテーブルとリレーションシップを作成・紐づけを行う必要があります

リレーションシップを作成するには、以下の画面で操作が必要です

こちらの画面で売上データの「日付データ」とカレンダーテーブルの「Date」を紐づけます

操作はいたって簡単です

該当する列の上でドラックをするだけです

これで下の画像のようなリレーション(売上データ:多⇒Calendar:1)が完成します

これで売上データとカレンダーテーブルを一緒に活用できるようになりました

では、レポート画面で「マトリックス」を作成してみましょう

マトリックスの行にカレンダーテーブルの「YEAR」を配置します

YEARの配置もドラックで行います

そしてマトリックスの値に売上データの売上金額を配置します

すると上記の画像のように「年」毎に売上金額の合計が算出されています

これはカレンダーテーブルと「多:1」の紐づけが行われているからです

では試しに、リレーションを消してみます

すると、以下のように2021年も2022年も同じ金額になってしまいます

これは、年ごとの多:1の紐づけが行われないまま、単純に売上金額の合計が算出されるからです

<まとめ>

今回はカレンダーテーブル自体を作成しました

エクセルと違い、テーブルも列も一から作る点がとても斬新だと思います

ただ、これだけだとまだ実用的なカレンダーテーブルとはいえません

次回、調整を加えていきたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

カレンダーテーブルを実用的に調整する~カレンダーテーブルを活用するツアー~

前回はカレンダーテーブルをDAXを使用して作成しましたが、今回は更に調整を加えてカレンダーテーブルを実用的なものにします

前回のままだと2点問題があります

1点は月が1→12という風に並んでしまう点です

本来は4→12→1→3という風に並べる必要があります

これは数字/MONTHの大きさしか並び順の判断基準がないからです

2点目はドリルダウンの仕組み(枝分かれの階層化)の仕組みを活用できないことです

これは「階層」が作られていないからです

「階層」の詳細は後述致します

今回は上記の2点に取り組みたいと思います

MONTHの並び順

MONTHの並び順はSWITCH関数を使用して、順位を変えるための列をカレンダーテーブルに追加します

Switch(MONTH([Date]),1,10,2,11,3,12,4,1,5,2,6,3,7,4,8,5,9,6,10,7,11,8,12,9))

SWITCH関数は指定仕方に少し癖があります

上の画像の1と10の箇所は、MONTH([Date])が「」の場合には「10」を出力するという意味になります

上の画像では「」の場合には「11」を出力するという意味になります

指定する順番が少しわかりにくいかもしれません

ただ、この順位の列を追加しただけでは、MONTHの順番はかわりません

別の箇所で順位を指定します

こちらの機能を使うには、まずは①対象列の選択:順位を変えるMONTH列を選択します

次に②順位を指定:前述の「列で並べ替え」で実際にSWITCH関数で作成した「順位」列を指定します

これでMONTH列の順番が正しく並ぶようになります

階層の作成

前述の階層とは、下の画像のような「QUARTERMONTHDate」のひとまとめの組み合わせのことです

こちらはリレーションシップの管理画面で作成します

まず、フィールドの欄にて「Date」の列を右クリックします

ここで「階層の作成」が表示されます

では、この階層の作成をクリックしてみます

これで下の画像のように実際に階層を作成する画面がでてきます

こちらの下の画面で列を追加しながら上下の階層を設定します

下の画像のようにドラックで上下の位置は調整できます

全て設定したら「レベルの変更を適用します」をクリックします

するとレポート画面にも「階層」が反映されます

これで、レポート画面にて実際に「行」に階層を設定するとドリルダウンの仕組みが使えるようになります

<まとめ>

今回は「列の順位の変更」と「階層」の作成により、カレンダーテーブルを更に使いやすくしました

ただ、前回も含めるとカレンダーテーブルの作成・調整にかなりの時間がかかってしまいます

カレンダーテーブルの作成のみの箇所で言えば、下のように変数などを使用してテンプレート化することは可能です

こちらはメモ帳をダウンロードできるようにしておきますので、修正しながら活用してみてください

次回はメジャーという仕組みを使って時系列に関する数字を自動的に抽出します

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ