LuckyTemplates のヒント: 最初の N 営業日を比較する方法

のメンバーのために私が思いついた最近の LuckyTemplates のヒントについて話したいと思います。この質問は、特定の月の最初の N 営業日または最初の N 請求可能日と前月の同じ期間を比較するという点で有用なパターンを扱いました。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

TJ Henneman は、当月の最初の 5 つの請求可能日と、前月の最初の 5 つの請求可能日、および 10 日目、15 日目、および 20 日目を比較したいと考えました。彼は請求可能日のみを見たいことに注意してください。したがって、これには週末と休日は含まれません。彼の投稿をチェックできます。

ここには非常に興味深いパワークエリソリューションがあると思います。この問題については、興味深いDAX ソリューションも使用しました。後者については別のチュートリアルで説明します。

また、 LuckyTemplates チャレンジ #16 で私が使用した最近のレポート手法についても説明したいと思います。私は、この特定の問題に非常にうまく機能するスクロールKPIインジケーターを使用しました。

ただし、この LuckyTemplates のヒントでは、パワー クエリ ソリューションについて触れます。LuckyTemplates に飛び込んで、データを見てみましょう。今回は、LuckyTemplates でまとめた練習用データセット ツールを使用してデータをシミュレートしました。このツールは無料で使用できます。これは、基本的なスター スキーマ データセットをまとめたり、ソリューションをテストしたりするのに最適です。

目次

• 私たちのデータモデル
• 重複テーブルの作成
• 日付テーブルのマージ
• カスタム列の作成
• What-If パラメータの作成
• 営業日数のメジャーの作成
• 結論

私たちのデータモデル

データ モデルは、拡張日付テーブルと一連のディメンション テーブルを使用します。また、初年度は 3 年間の部分データ、2 年目は完全データ、3 年目は部分データを含む Sales テーブルもあります。最後に、 Measures テーブルもあります。

このデータセットも少し変更しました。この分析で削除する休日を適切に計算できるように、休日テーブルを取り込んで拡張日付テーブルにリンクしました。これを行う方法がわからない場合は、このチュートリアルを参照してください。

次に、データセット内の最初の部分的な月をフィルターで除外しました。その月にはデータが数日しかなく、必要な営業日がなかったためです。最初の丸 1 か月から開始することに注意してください。ただし、それ以外は、これが標準のデータセットになります。それでは、 Power Queryに移り、この LuckyTemplates のヒントに取り組み始めましょう。

データを見てみると、特定の日に異なる注文があることがわかります。目標は、4 月 1 日を最初の営業日、4 月 2 日を 2 番目の営業日、4 月 3 日を 3 番目の営業日として、すべてに番号を付けることです。土日祝日を除いて、各月に 1 ～ N 営業日の番号を付けます。

重複テーブルの作成

まず、このテーブルの複製を作成して、Total Salesを集計し、それを個々の観察レベルに取り込む必要があります。この重複テーブルをSales Aggregatedと呼ぶことにします。

お気づきかもしれませんが、Sales テーブルを単に参照しているのではなく、複製しています。この場合、このテーブルを元の Sales テーブルと結合します。それを参照すると、両者を結合するときに問題が発生します。

最初に行うことは、OrderDate 列でGroup Byを使用することです。ここで行っていることの全体像が表示されるので、「詳細」ボタンをクリックします。新しい列名としてTotal Day Salesを入力します。これは行の合計の合計になります。

これで、各日付 (最初の列) とその日の総売上高 (2 番目の列) が得られます。

日付テーブルのマージ

この LuckyTemplates ヒントの次のステップは、これをDates テーブルとマージして、どの日が営業日でどの日がそうでないかを判断することです。もう一度Group By して、それらを分割し、適切に番号を付けることができます。

Dates テーブルをマージし、OrderDateをDateにリンクします。下部では、選択内容がテーブルのすべての行に正しく一致していることがわかります。

私たちが主に関心があるフィールドはIsBusinessDayです。

これはグループ化する主なフィールドであり、その後、Month & YearフィールドとMonthn Yearフィールドを使用します。

また、 DayofWeekNameをチェックして、適切な日がフィルターで除外されていることを確認する必要があります。

[OK] ボタンをクリックすると、必要なものが正確に表示されます。

また、注文日が昇順に並べ替えられていることを確認する必要もあります。

次に、戻って 2 番目のGroup Byを作成します。

今回は、[詳細設定] ボタンをクリックし、[月と年]でグループ化し、2 番目のグループを追加します。

2 番目のグループ化はIsBusinessDayのため、営業日に該当するグループに番号を付けることができます。

次に、 AllRows 関数を作成します。これにより、集計が行われず、元の粒度レベルに戻ることができます。

これにより、月ごとにtrueのテーブルとfalseのテーブルを確認できるネストされたテーブルが得られます。

カスタム列の作成

次に、営業日に必要なカウントを追加するカスタム列を作成する必要があります。

AllRows テーブルを操作するTable.AddIndexColumnという関数を使用します。これをDay Indexと呼びます。インデックスは 1 から始まり、新しい営業日ごとに 1 ずつ増加するようにします。

次に、最初の 3 つの列を削除し、4 番目のカスタム列を展開します。

結果を見ると、営業日 (true) をカウントし、各月の週末と休日の日 (false) をカウントする日インデックスが得られます。次に、これらが適切なフィールド タイプであることを確認する必要があります。

Sales テーブルに戻りましょう。作成したばかりの Aggregate テーブルを Sales テーブルとマージします。

これらをOrderDateにマージします。下部に、最初のテーブルのすべての行と一致していることがわかります。

Sales Agg 列を展開するときに、追加したばかりの新しいフィールド、Month & Year、DayOfWeekName、Monthn Year、IsBusinessDay、およびDayIndex列を選択します。

[閉じて適用]をクリックする前に、データをクリーンアップしましょう。Sales Agg テーブルをデータ準備セクションに移動し、データ モデル内でそのテーブルが必要ないのでアンロードしましょう。次に、「閉じて適用」をクリックします。

これで電源クエリの準備作業は完了です。

What-If パラメータの作成

元のタスクでは、調査する日数を 5 から 10、15 から 20 に変更したいことを思い出してください。これを行う最良の方法は、新しい what-if パラメーターを使用することだと思います。

what-if パラメーターを「最初の N 営業日」と呼び、そのデータ型は整数になります。最小値は 1 ずつ増加し、最大値は 20 に設定されます。その後、デフォルトでユーザーが希望する最初の値である 5 に設定します。

これで、スライサー値を取得するための収穫メジャーを自動的に作成するスライサーが完成しました。

結果を含むテーブルを作成しましょう。Month & Yearディメンションをキャンバスに配置し、Month & Yearで並べ替えてみましょう。

このフィールドを適切に並べ替えたら、Total Salesメジャーを削除して展開するだけです。

営業日数のメジャーの作成

スライダー パラメーターの値を調べ、営業日数のみを計算するメジャーをもう 1 つ作成する必要があります。

この新しいメジャーをTotal Sales N Bus Daysと呼びます。関数と Total Sales メジャーから始めます。これをSales テーブルのでフィルタリングします。

次のステップでは、スライダーに対して考慮する条件を記述します。Power Query で作成した Sales Day Index があり、これをスライサーの収集値である最初の N 営業日の値以下にする必要があります。

たとえば、スライダーが 5 の場合、Sales テーブルの日インデックスが 5 以下であるすべての日が必要になります。

それから、非営業日を除外したいと考えています。Date テーブルを参照し、IsBusinessDay フィールドを使用します。TRUE である結果のみを見ていきます。

最後に、月と年のコンテキストを戻してメジャーを終了します。この時点では問題なく進むはずです。

新しいメジャーをテーブルにドロップしましょう。

スライダ スライダを 10 日のマークまで移動すると、テーブルは 10 日のマークで動的に計算されます。

結論

この LuckyTemplates のヒントでは、Power Query ソリューションを使用して動的に類似比較を行う方法について説明しました。これを棒グラフまたは折れ線グラフに入れることができます。

今後のチュートリアルの一部では、DAX ソリューションを見て、KPI スクローラーの視覚化を実装します。

この特定のチュートリアルで取り上げたコンテンツを気に入っていただけた場合は、 LuckyTemplates TVチャンネルに登録することを忘れないでください。