Power BIで毎月末の数字をつかって、前月からどれだけ増えたかをDAX使ってグラフ化する

こんな人のための記事です

毎日種別にかならず増加する数字をつかって、当月の最大値と前月の最大値を比較し、その差を求めたい人のための記事です。

実際に相談された例は、SharePointサイトの各ページの閲覧数を毎日記録している表を使って、各ページの月間閲覧数をページ単位で出したいというリクエスト。

最初は複雑に考えたのですけれど、閲覧数は必ず増えていくので、各月の最大値を比較するだけと気が付いて片付きました。地味に使えそうな例なので自分のための備忘録を兼ねて記事にしました。

サンプルデータです

今回はこんな感じのサンプルデータです。テストデータなので、毎月1日と2日しか書いてませんが、月末まで各ページの閲覧数がミッチリ続いているようなイメージをしてください。

たとえば、a.aspxのページは1月には12が最大値。2月は30が最大値。3月は54が最大値になっているのがわかります。b.aspxも、c.aspxもそれぞれに各月の最大値を持っています。

日付を日時型にしてカレンダーテーブルとリンク

DAXを使う前に、日付列の型を日時に変更しておきます。これはこの後行うカレンダーテーブルとリンクさせるためです。
ページ列はテキスト、閲覧数は整数にしておくのを忘れずに。

定番のカレンダーテーブルとリンク

いつもQiitaのこちらの記事からカレンダーテーブルのDAXを使わせていただいてます。

モデリング、あたしいテーブルをクリックして・・・

先ほどのページからコピーしたカレンダーテーブルのDAXをぺたりと貼り付けます。StartDateは扱うテーブルの最初の日付あたりに。EndDateはUTCNOW()　関数を設定しておくのが良いでしょう。

カレンダーテーブルが出来上がったら、日付テーブルとしてマークするのを忘れずに。

「日付テーブルとしてマーク」のトグルをオンにして、日付列として「Date」を選択し、保存します。

二つの日付の列を多対一でリンクさせます。

こんな感じになれば準備完了です。

さっそくカレンダーテーブルの年、月をつかいつつ、ページと閲覧数の列を当てはめてみます。カレンダーテーブルとのリンクがうまくいってればこんな感じで表示されるはずです。この段階で表示されない場合は、２つのテーブルの日付列の型が異なっているかもしれません。

閲覧数を合計から最大値に切り替えてみる

先ほどは閲覧数の列に「合計」が選択されていましたが、視覚化の列部分で閲覧数を「最大値」に切り替えると、いい感じに各月の最大値が表示されました。サンプルでは２日分しか用意しませんでしたが、月末の数字になっているイメージです。

閲覧数の最大値をDAXに書き換える

資格化では列の部分で選択するだけで合計や最大値を求めることができますが、あえてメジャーを使って書き換えます。閲覧Dataテーブルの右側にある三点リーダーから、「新しいメジャー」を選択します。非常にシンプルですが、下記のように記述します。

M_閲覧数最大値 = MAX([閲覧数])

メジャーが作成できたら、閲覧数部分を今作ったメジャーに差し替えます。表示されているテーブルに変化がないことがわかります。

前月の最大値を並べて表示させる

各月、各ページごとの最大値が表に表示できました。今度はその右側に前月の数字を表示させましょう。１か月ずらして表示させるだけです。

ふたたび、「新しいメジャー」から以下のように記述します。用いるのは先ほど作成した最大値のメジャーです。テーブルに表示させてみると、当月の最大値と行が１月分ずれているのがわかります。

M_前月閲覧数 = CALCULATE([M_閲覧数最大値],DATEADD('日付テーブル'[Date],-1,MONTH))

2列をメジャーで作れたら、あとは差を出すだけ

当月と先月の最大値を求められたので、後は差を求めるだけです。これもメジャーを使います。とってもシンプルです。

前月との差 = [M_閲覧数最大値] -  [M_前月閲覧数]

最後に、4月の部分はまだ実績がないので空欄になっています。フィルタを使ってM_閲覧数最大値列が空の場合は見えなくしてしまいましょう。

グラフにしてみよう

準備ができたメジャーを使ってグラフにしてみましょう。
X軸は年と月をカレンダーテーブルから配置します。
Y軸は前月との差、つまり月間の閲覧数です。
凡例にページを指定してやると、各ページの閲覧数が分けて表示されます。

配置してみると、一番最新の月がマイナスの値になっていますね。これはテーブルと同じくM_閲覧数最大値の当月が空欄になっているからです。グラフにもフィルターを適用してやりましょう。M_閲覧数最大値が「空白ではない」にしてやると、いい感じにグラフが表示されました。

おしまいに

PowerQueryでも同じことはできるはずですが、メジャーは表示されるときに評価されます。いっぽうPower Queryでは列を増やせばそれだけデータ量が増えてしまいます。わたしもDAXは苦手なんですが、最後に名言を。
「にげちゃだめだ　にげちゃだめだ・・・」
お粗末様でした。