この記事はMicrosoft Power BI Advent Calendar 2018に参加しています。
累計グラフをPoewr BIってみんとする
↑こんな感じの前年・当年・予算の累計グラフが大好物な弊社。
今まではPower BIでの作成方法がわからず、お得意のAccessで加工した元データを、Excelに出力して、VBAで足し算させていくという方式で更新していましたが、そろそろPower BI力を上げていかねばと一念発起して、チャレンジしてみました。
自分用語でレポート作成画面を「表」、クエリエディター画面を「裏」って呼んでます。
技術的なことよりも、あんまり何も考えずに作ると、裏でも表でも項目作ったりしちゃうよね、ほんとはどっちかでまとめた方が後からみてわかりやすいんじゃないかなってお話です。
まずは裏で
まずは裏で、当年と前年を識別するフラグ項目を作成。裏の方が慣れているので、必要な項目は全部裏で作っちゃおうと思いました。
「当年」という項目を作って
DateTime.ToText(DateTime.LocalNow() + #duration(0,9,0,0),"yyyy")
「年度抽出」という項目でフラグをたてます。
if [当年]=[YYYY]
then 1
else
if [当年]-1 =[YYYY]
then 2
else 0
それから表でメジャーを作ろうと思ったら・・・・
これで準備はオッケー！とクエリの適用をして表へ。
早速前年、当年、予算の累計を出すメジャーを！
・・・と思ったらなんかうまくいかない。
あれこれ試行錯誤して、前年の日付をと今年の日付に読み替えてあげなくちゃと思い、また裏に戻って項目つくるの面倒だ・・・・
項目は裏でまとめて作ろうと思ったのに、あっさり表でつくっちゃうことにしました。
グラフ用日付 = if('実績'[年度抽出]=2,
DATE(YEAR('実績'[Invoice_Date])+1,
MONTH('実績'[Invoice_Date]),
DAY('実績'[Invoice_Date])),'実績'[Invoice_Date])
年度フラグが2（前年）だったら、Invoice_Dateに１年足す。
それから表でメジャーを作ろうと思ったら・・・再び
これで日付が揃ったので、やっと累計のメジャーを書く。
前年累計 = CALCULATE(sum('実績'[金額]),'実績'[年度抽出]=2,
FILTER(ALLSELECTED('実績'),'実績'[グラフ用日付]<=MAX('実績'[グラフ用日付])))
当年累計 = IF(MAX('実績'[グラフ用日付])<=TODAY(),
CALCULATE(sum('実績'[金額]),'実績'[年度抽出]=1,FILTER(ALLSELECTED('実績'),
'実績'[グラフ用日付]<=MAX('実績'[グラフ用日付]))),BLANK())
最後の「BLANK()」がないと今日以降が平らになってしまうので、今日以降の線を引かせないためです。
まだまだ足りない項目・・・
実績は実績テーブルから、予算は予算テーブルから、それをカレンダーテーブルでリレーション。
さて、グラフに値を配置・・・・
と思ったら、使っているカレンダーテーブルの日付が「YYYYMMDD」形式になってるのでなんかうまくない。
で、YYYYMMDD形式を日付形式に変換。
これでやっとこさ完成！
もっとスマートな方法もあるかと思うのですが、これでよしとする！
まとめ
結論としてはたぶん表でやっても裏でやってもできることはどっちでやってもいいのでしょうが、どちらかに寄せた方がいいのではないかと思いました。
しかし、もともとプラグラマー脳みたいなものが未発達なせいか、必要な事前準備がきちんとできずにどうしても手戻りが発生してしまいます。
項目名なんかもテキトウになっちゃうので、あとから見て「？」となってしまう・・・
これは慣れていけばできるようになるものなのか、それともちゃんと訓練（？）しないとダメなものなのでしょうか・・・・？
（初Qiita記事でレイアウトなどお見苦しいところがあるかと思いますが、やさしい気持ちで読んでいただけるとありがたいです。）