【PowerBIで行の計算】行同士を比較して日付の差分を計算

PowerBI 行の計算 LOOKUPVALUE DATEDIFF RANKX関数 行番号 行計算Power BI
スポンサーリンク

PowerBIは列の計算は得意ですが、行の計算って難しいですよね…。そんな時に、「前の行との差分を比較」、「条件を指定した行の比較」などの、PowerBIで行計算をする方法を紹介します。

【本記事でできること】
・PowerBIで行同士の計算ができる
・1行前の日付と比較して購入間隔を計算する
・条件を指定した行の計算
・RANKX関数、LOOKUPVALUE関数、DATEDIFF関数の応用

PowerBIで行と行を計算する方法はコツがいるので、ぜひご参考ください!

PowerBIで行の計算(購入間隔の計算)

まずは、1行前の日付と比較して、購入間隔を計算する事例を紹介します。
1行前と現在の行の値を計算するステップは下記の①~④です。

①RANKX関数で日付順に番号をつける
②前の行フラグとして、日付順番号から1を引く
③LOOKUPVALUE関数で前回購入日を取得する
④DATEDIFF関数で日付間隔を日数で表示する

◆使用するデータ
今回使用するデータは、「購入日」「売上」「店舗コード」「会員番号」が入った顧客購入データです。こちらをPowerBIに取り込んでいる状態です。

PowerBI 使用データ 顧客データ

◆完成系 1行前の購入日と比較し購入間隔を計算
DAX関数で行フラグを作成し、前回購入日を計算し購入間隔を取得します。

PowerBI 行計算 行と行 1行前 RANKX LOOKUPVALUE DATEDIFF

また、作成した購入間隔(日)のデータをダッシュボードでテーブル追加していきます。

PowerBI 行計算 日付間隔 行の差 テーブル 条件付き書式

各①~④の手順で必要なDAX関数やテーブル作成方法を下記の記事でまとめていますので、ぜひご参考ください!

【関連記事】
▶RANKX関数の使い方、フィルターを設定した順位付け
▶LOOKUPVALUE関数の使い方、データの連携方法
▶DATEDIFF関数で日付間の差分を年/月/日で取得
▶PowerBIでテーブルの作成方法、条件付き書式の設定
▶PowerBIでマトリックス表の作成方法(ヒートマップ的な在庫管理表の作成)

それでは、実際に行の計算をして、前回購入日との日付間隔を計算する手順を説明していきます。

スポンサーリンク

①RANKX関数で日付順に行番号をつける

まずは、PowerBIに取り込んだデータを、RANKX関数で日付順に行番号のデータ列を作成していきます。

RANKX関数の使い方は下記の通りです。

【RANKX関数】指定した式の順位を返す関数

RANKX(①テーブル, ②式, ,③降順/昇順, ④同一順位の設定)
①table:テーブル
②expression(式):順位付けする値の計算式、基本はメジャーを指定
③order:降順/昇順であるDESCかASCを指定
 DESC:descending order、ASC:ascending order
④ties:同一順位の設定でSkipかDenseを指定

RANKX関数の使い方、使用する際の注意点をまとめているので、ぜひご参考ください!

Microsoft DAX関数 RANKX関数

https://learn.microsoft.com/ja-jp/dax/rankx-function-dax

構文に従って、データビュー画面にRANKX関数でデータ列を作成します。

PowerBI RANKX関数 行番号
日付順 = RANKX('顧客購入データ','顧客購入データ'[購入日],,ASC,Dense)

①テーブル:’顧客購入データ’
②式:’顧客購入データ'[購入日]
(購入日の順番に行番号をつけたいため、購入日データを指定)
③降順/昇順:日付の小さい順なので昇順(ASC)を指定
④同一順位の設定:今回は同一の行が無いのでどちらでもOKです。

「日付順」というデータ列を追加し、行フラグを作成できました!

PowerBI RANKX関数 行番号 日付順

【結論】RANK関数で行番号のフラグを作成する

スポンサーリンク

②前の行フラグとして、日付順番号から1を引く

前の行と比較したいので、作成した行フラグから1を引き、1行前を指定するフラグを作成します。

単純に先ほど作成した行フラグ引く1の「日付順-1」の列を追加します。

PowerBI 行フラグ 前の行
日付順-1 = '顧客購入データ'[日付順]-1

前の行のフラグを追加できました!

PowerBI 前の行フラグ 行の計算

【結論】作成したフラグから1を引いて、1行前のフラグを作成する

③LOOKUPVALUE関数で前回購入日を取得する

前の行のフラグを作成できたので、前の行フラグをキーにLOOKUPVALUE関数で前回購入日を取得すればOKです!

LOOKUPVALUE関数の使い方、構文は下記です。

【LOOKUPVALUE関数】検索キーでデータ連携する関数

LOOKUPVALUE(①返す値,②検索キー,③追加する側の検索キー)
今回の場合は、
①返す値:今回は購入日
②検索キー:フラグである日付順
③追加する側の検索キー:フラグである日付順-1

LOOKUPVALUE関数の使い方を下記で詳しく説明していますので、ご参考ください。

LOOKUPVALUE関数で前の行の購入日である「購入日(前回)」のデータ列を作成します。

PowerBI LOOKUPVALUE関数 行計算 前の行
購入日(前回) = LOOKUPVALUE('顧客購入データ'[購入日],'顧客購入データ'[日付順],'顧客購入データ'[日付順-1])

LOOKUPVALUE関数で前回購入日を取得することができました!

LOOKUPVALUE関数 PowerBI 前回購入日

【結論】LOOKUPVALUE関数で1行前のフラグから前回購入日を取得する

スポンサーリンク

④DATEDIFF関数で日付間隔を日数で表示する

前回購入日を取得できたので、DATEDIFF関数で日付間の差分を日数表示すれば完了です!

DATEDIFF関数の構文と使い方は下記です。

【DATEDIFF関数】2つの日付間の差を計算する関数
DATEDIFF(開始日付,終了日付,間隔)

DATEDIFF関数に開始日付、終了日付を入れて、差分を日数で表示したいので、間隔をDAYにします。

購入間隔(日) = DATEDIFF('顧客購入データ'[購入日(前回)],'顧客購入データ'[購入日],DAY)

「購入間隔(日)」というデータ列に日付間の差分を日数で取得できました!

DATEDIFF関数 PowerBI 行計算 前の行 1行前 日付 差分 日数

【結論】DATEDIFF関数で日付間隔を日数で取得する!

この、①~④のステップで前回購入日、購入間隔を取得できました!

あとは、このデータを用いて、ダッシュボード画面で見やすい形にテーブル作成をしていけばOKです!

PowerBI 行の計算 購入間隔 テーブル
スポンサーリンク

PowerBIで条件を指定した行の計算

次は、応用的に条件を指定した行の計算方法を紹介します。

先ほどのデータを使って、同じ店舗の中での購入間隔を計算させます。下記のように、同じ店舗という条件内で前回購入日を取得して、購入間隔を表示します。

PowerBI 行の計算 間隔 条件付き

考え方は先ほどと同様ですが、下記の点に注意が必要です。

①パワークエリで店舗名順に並べかえる(昇順)
 →同じ店舗名を固めるため
②パワークエリで日付の古い順に並べる(昇順)
③パワークエリで行番号をつける

④店舗名&行番号でフラグを作成
⑤店舗名&行番号-1で前購入日のフラグを作成
⑥フラグからLOOKUPVALUE関数で前回購入日の取得
⑦DATEDIFF関数で購入日間隔を取得

④~⑦は先ほどと同様の考え方ですね!①~③のクエリを使ってひと工夫することが重要です。これはフラグの作成時に、条件別に固めていないと、うまくフラグが機能しないためです。

スポンサーリンク

パワークエリで条件順に並べかえ

まずは、条件別のフラグ作成の準備をパワークエリで進めていきます。

①パワークエリで店舗名順に並べかえる(昇順)
パワークエリを開き、店舗コードの「▼」をクリックします。

PowerBI パワークエリ 昇順 降順

「昇順で並び替え」をクリックすると、店舗名順に並び替えることができます。

PowerBI 昇順で並び替え 降順 列 並び替え 条件

②パワークエリで日付の古い順に並べる(昇順)
同様に、購入日列にも「昇順に並び替え」をクリックします。

PowerBI 昇順で並び替え 降順 列 並び替え 日付順

これで、フラグをつけるための準備として、店舗名かつ日付順に並び替えることができました!

パワークエリ 並び替え 昇順 降順 行の計算 条件

【結論】条件付きの行の計算は、事前にパワークエリで順番を並び替えておく

スポンサーリンク

条件別に並び替えたデータに行フラグを追加

続いて、並び替えたデータに行番号を追加していきます。

③パワークエリで行番号をつける
行番号は「列の追加」→「インデックス列」→「1から」で追加できます。

PowerBI PowerQuery パワークエリ 行番号 インデックス列

並び替えたデータに、フラグの元となる行番号の列を追加することができました!

PowerBI PowerQuery パワークエリ 行番号 インデックス列 行フラグ

あとは、パワークエリ画面を閉じて、先ほどと同様にデータ画面からフラグ列を追加して、購入間隔を取得すればOKです。
④店舗名&行番号でフラグを作成
→店舗コード&”‐”&行番号
⑤店舗名行番号-1で前購入日のフラグを作成

→店舗コード&”‐”&行番号-1
⑥フラグからLOOKUPVALUE関数で前回購入日の取得

→LOOKUPVALUE(購入日,フラグ,前回購入日フラグ)
⑦DATEDIFF関数で購入日間隔を取得

→DATEDIFF(前回購入日,購入日,DAY)

パワークエリで作成した行番号を用いて、購入間隔を日数で取得することができました!

PowerBI 行フラグ 行の計算 LOOKUPVALUE LOOKUPVALUE

こちらを、先ほどと同様に、レポート画面でテーブル作成すれば、店舗名ごとの行計算をした購入日間隔を取得することができました!

PowerBI 日付間隔 DATEDIFF LOOKUPVALUE 条件付き 行の計算

【結論】
並び替えたら、パワークエリでフラグ用の行番号をつける
条件別のフラグを作成してLOOKUPVALUE、DATEDIFF関数で日付の差分を取得

スポンサーリンク

PowerBIで行と行の計算 まとめ

今回はPowerBIで行の計算をする方法を紹介しました。ポイントは下記の3点です。
・行の計算は1行前フラグを作成する
・RANK、LOOKUPVALUE、DATEDIFF関数を使って購入間隔を取得
・条件付きの行の計算はパワークエリで並び替えをしておく

PowerBIやPowerQueryは列の計算を基本としますが、今回のように行の計算をしたい場面が出てきます。そんな場合に、今回の行フラグを作って計算する方法をぜひ試してみてください!

下記でPowerBIの便利機能、DAX関数を全てまとめています。ぜひご参考下さい!

▶PowerBI、PowerQueryの便利機能、学習方法まとめ

よく使うDAX関数まとめ

良ければ、クリック応援をよろしくお願いします!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

コメント

タイトルとURLをコピーしました