[[20210421161107]]　『パワーピボットでの前回購入日と日数計算』（kon）　ページの最後に飛ぶ

『パワーピボットでの前回購入日と日数計算』（kon）

はじめまして。

パワーピボットの初心者です。

以下のデータを用意して、購入回数、最終購入日、前回購入日からの日数計算を担当者別に算出したいと考えています。

シートデータ：
ID　顧客名　担当
1、A、伊藤
2、B、長井
3、C、岡崎

数値データ：
ID　　顧客名　購入日　
1、A、2019/01/05
1、A、2019/05/15
1、A、2019/07/20
2、B、2018/02/01
2、B、2020/06/01
3、C、2020/09/05
3、C、2021/04/03

IDをリレーションで結び、
ID、名前、購入階数、担当、最終購入日の順でピボットテーブルを作成しました。

1、A、3、伊藤、2019/07/20
2、B、2、長井、2020/06/01
3、C、2、岡崎、2021/04/03

ここまでは出来ましたが、前回購入日と、前回からの間隔がどうしても出せません。

元となるデータの作成が間違っているのかもしれませんが、
ご教授いただければ幸いです。

よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

その例に沿って、どのような結果を得たいのか、具体的に示されたらいかがですか？
双方の思い違いを回避するには、できるだけ具体的に説明されたほうがいいですよ。

（γ） 2021/04/21(水) 18:21

わたしのPCでは、Power Pivotが使えないので
Power Queryで試してみましたが
こんな感じなら可能です。

 ID  顧客名   購入回数   担当    最終購入日    前回購入日    購入間隔
 1     A         3       伊藤    2019/7/20      2019/5/15      66
 2     B         2       長井    2020/6/1       2018/2/1       851
 3     C         2       岡崎    2021/4/3       2020/9/5       210

（マナ） 2021/04/21(水) 21:28

ご返答ありがとうございます。

＞γ様
確かに具体的な結果例を示すべきでした。
失礼しました。

＞マナ様
まさしく希望の通りの表です！
Power Queryで完結できるんですね。
一度色々いじってみます。もし分からなければ再度質問させてください。

（kon） 2021/04/22(木) 11:55

すみません、色々試してみましたが、前回購入日と購入間隔の出し方が分かりません。

上記の例で出した購入日のデータに顧客マスタデータをクエリで結合し、

ID、名前、購入日、担当者

1、A、2019/01/05、伊藤
1、A、2019/05/15、伊藤
1、A、2019/07/20、伊藤
2、B、2018/02/01、長井
2、B、2020/06/01、長井
3、C、2020/09/05、岡崎
3、C、2021/04/03、岡崎

というテーブルを作成しました。

その後、IDでグループ化すれば、購入回数と最終購入日を抽出できることが分かりましたが、
前回購入日と購入間隔の出し方が分かりません。

ご教授いただけると助かります。
よろしくお願いいたします。
（kon） 2021/04/22(木) 14:52

 前回購入日はどのようなレイアウトで入力されているのですか。
（*） 2021/04/22(木) 17:57

＞*様

ご返信ありがとうございます。

前回購入日としての列は作成していません。
上記で示したように、縦一列に購入日が並んでいます。

月初に前月の購入データを追加更新していくので、クエリ上で前回購入日も更新出来ればと思っていました。

以上、よろしくお願いいたします。
（kon） 2021/04/22(木) 18:12

一例です。

 ここを参考にしました
　https://cloud6.net/so/powerbi/1979179

 let
    ソース = Excel.CurrentWorkbook(){[Name="購入実績"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"ID", type text}, {"顧客名", type text}, {"購入日", type date}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [ID] <> null and [ID] <> ""),
    マージされたクエリ数 = Table.NestedJoin(フィルターされた行, {"ID"}, 顧客マスタ, {"ID"}, "顧客マスタ", JoinKind.LeftOuter),
    #"展開された 顧客マスタ" = Table.ExpandTableColumn(マージされたクエリ数, "顧客マスタ", {"担当"}, {"担当"}),
    グループ化された行 = Table.Group(#"展開された 顧客マスタ", {"ID", "顧客名", "担当"}, {{"group", each _, type table [ID=nullable text, 顧客名=nullable text, 購入日=nullable date, 担当=nullable text]}, {"購入回数", each Table.RowCount(_), Int64.Type}, {"最終購入日", each List.Max([購入日]), type nullable date}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "前回購入日", each List.Max(List.RemoveItems([group][購入日],グループ化された行[最終購入日]))),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "購入間隔", each Duration.Days([最終購入日]-[前回購入日])),
    削除された列 = Table.RemoveColumns(追加されたカスタム1,{"group"}),
    並べ替えられた列 = Table.ReorderColumns(削除された列,{"ID", "顧客名", "購入回数", "担当", "最終購入日", "前回購入日", "購入間隔"})
 in
    並べ替えられた列

（マナ） 2021/04/22(木) 18:46

もう一例。
少し長いですが、こっちの方法は、他でも使えると思います。

１）購入日を降順でソート
２）IDでグループ化
３）グループ内で連番作成
４）連番トップ２をフィルター
５）列のピボット

 参考：【Power Query】グループの中でインデックスを付ける
　https://papasensei365.com/powerquery-index-the-group/

 let
    ソース = Excel.CurrentWorkbook(){[Name="購入実績"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"ID", type text}, {"顧客名", type text}, {"購入日", type date}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [ID] <> null and [ID] <> ""),
    マージされたクエリ数 = Table.NestedJoin(フィルターされた行, {"ID"}, 顧客マスタ, {"ID"}, "顧客マスタ", JoinKind.LeftOuter),
    #"展開された 顧客マスタ" = Table.ExpandTableColumn(マージされたクエリ数, "顧客マスタ", {"担当"}, {"顧客マスタ.担当"}),
    並べ替えられた行 = Table.Sort(#"展開された 顧客マスタ",{{"購入日", Order.Descending}}),
    グループ化された行 = Table.Group(並べ替えられた行, {"ID"}, {{"group", each _, type table [ID=nullable text, 顧客名=nullable text, 購入日=nullable date, 担当=nullable text]}, {"購入回数", each Table.RowCount(_), Int64.Type}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([group],"index")),
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"group"}),
    #"展開された カスタム" = Table.ExpandTableColumn(削除された列, "カスタム", {"顧客名", "購入日", "担当", "index"}, {"顧客名", "購入日", "担当", "index"}),
    フィルターされた行1 = Table.SelectRows(#"展開された カスタム", each [index] < 2),
    並べ替えられた列 = Table.ReorderColumns(フィルターされた行1,{"ID", "顧客名", "購入回数", "購入日", "担当", "index"}),
    ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(並べ替えられた列, {{"index", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(並べ替えられた列, {{"index", type text}}, "ja-JP")[index]), "index", "購入日"),
    #"名前が変更された列 " = Table.RenameColumns(ピボットされた列,{{"0", "最終購入日"}, {"1", "前回購入日"}}),
    追加されたカスタム1 = Table.AddColumn(#"名前が変更された列 ", "購入間隔", each Duration.Days([最終購入日]-[前回購入日]))
 in
    追加されたカスタム1

（マナ） 2021/04/22(木) 19:14

↑どっちも、テーブル名は「購入実績」と「顧客マスタ」としています

（マナ） 2021/04/22(木) 19:20

＞マナ様

2例もご提示いただき、有難うございます。
頑張って試してみます。

また分からないことがあれば教えていただけると幸いです。

よろしくお願いいたします。

（kon） 2021/04/23(金) 18:06

コメント返信：

[ 一覧(最新更新順) ]

[[20210421161107]] 『パワーピボットでの前回購入日と日数計算』（kon） ページの最後に飛ぶ

[[20210421161107]]　『パワーピボットでの前回購入日と日数計算』（kon）　ページの最後に飛ぶ