DAX と Power Query を使用して LuckyTemplates の前週の値を取得する

LuckyTemplates で前の期間の値を取得するのは一般的なタスクですが、前週の値を取得するには特別な注意が必要です。このチュートリアルでは、ツールボックスへの追加として役立つ LuckyTemplates の前週の値を取得するための 2 つの簡単で汎用性の高い方法について説明します。1 つは DAX を使用し、もう 1 つは Power Query を使用します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

このチュートリアルは、LuckyTemplates メンバーからの質問から生まれました。メンバーは、前週の数字を確認する最も簡単な方法は何かと尋ねました。これは私がよく遭遇する質問です。とてもシンプルに見えますが、詳しく調べれば調べるほど、処理すべきことがかなりたくさんあることがわかります。

目次

• LuckyTemplates の前週の値: 問題
• DAX を使用した前週の LuckyTemplates の取得
• 結論

LuckyTemplates の前週の値: 問題

週番号を見ると、52 週あります。これに 7 日 (1 週間) を掛けると、364 になります。つまり、各年には 1 日余分に存在し、閏年には 2 日余分に存在します。この 1 ～ 2 日の余分な時間が、最終的には多くの問題を引き起こすことになります。

Dates テーブルからメジャーを作成しましょう。これをMAX Week # (数値)と呼びます。この分析では、LuckyTemplates Extended Dates Table を使用しています。これにはISO Weeknumber が含まれており、非常に役立ちます。ここで、ISO Weeknumber の MAX を取得してみましょう。

ここで、テーブルを取得し、YearとMAX Week #をドロップしてみましょう。このデータ セットには 10 年間のデータが含まれており、最初の 2 年間は 52 週間であることがわかります。次に、53 週で 2 年、52 週で 3 年、さらに 53 週で 2 年、52 週で 1 年になります。

ここには実際に識別できるパターンはありません。過去に遡って、ある年は 52 週になり、別の年は 53 週になると予測するのは簡単ではありません。

DAX タイム インテリジェンス関数に慣れている場合、次に思いつくのは、日付の範囲を指定された期間だけ前にシフトするDATEADD関数の使用です。日、月、四半期、年については非常にうまく機能しますが、週は含まれていないため、これを行う別の方法を見つける必要があります。

私たちが使用している LuckyTemplates 拡張日付テーブルには、Melissa (LuckyTemplates エキスパートの 1 人) がOFFSETSと呼ばれる関数を組み込みました。この関数は DAX の観点からこれを解決する上で絶対に重要であるため、この関数について詳しく見ていきましょう。

現在の期間 (たとえば、このチュートリアルの作成期間である 1 月 20 日) を見ると、offset が 0 に等しいことがわかります。オフセットの仕組みとしては、現在の期間ではオフセットがゼロになります。

現在の週の前の週はマイナス 1、現在の週の 2 週間前はマイナス 2、というようになります。そして、正の数から順に進みます。次の週には 1 が付けられ、2 週間には 2 が付けられます。

今日の日付を基準にしてレポートを開くたびに、オフセットが計算���れます。これは信じられないほど強力です。オフセットを使用すると、この LuckyTemplates の前週番号のようないくつかの難しい問題にいかに簡単に取り組むことができるかを説明します。

DAX を使用した前週の LuckyTemplates の取得

新しいメジャーを作成して、「Previous Wk #」という名前を付けてみましょう。変数 ( SelWkOffset )から始めて、 WeekOffsetのSELECTEDVALUEを実行するだけです。したがって、現在の行で WeekOffset を描画し、それを変数に入れます。

次の変数はResultになります。これが私たちが望む最終的な結果であり、コンテキストを変更するのでCALCULATEを使用します。

次に、 MAX ISO Weeknumber が必要であり、これはこれらのフィルター条件の対象となります。したがって、Dates テーブルのすべてのフィルターを削除するため、ここではALLを使用します。日付スライサーがある場合は、 ALLSELECTED を使用したくなるかもしれませんが、今のところはそうではないので、ALL を使用してその Dates テーブルからフィルターを削除するだけです。

そして、WeekOffset が SelWkOffset から 1 を引いた値に等しくなるとしましょう。ここで、OFFSET が非常に強力になります。Weeknumber は毎年リセットされるため、Weeknumber から 1 を引いた値は使用できません。ただし、ALL OFFSETS は連続しているため、前年を検索して前年ごとに 1 を引くのと同じように扱うことができます。したがって、このオフセットは、連続する番号とまったく同じように機能します。

これで、フィルター条件を取得して閉じ、 CALCULATE を閉じて、Resultを返すだけです。

では、この対策がどのように機能するかを見てみましょう。以下の表では、まさに期待どおりの動作をしていることがわかります。翌年の期間 1 の第 52 週では、前の週は 52 になります。その後、期間ごとに 1 つ戻り、以下同様になります。

では、オフセットがない場合はどうすればよいでしょうか?

データ ウェアハウスから取得した企業の日付テーブルを操作していて、これらのオフセットが含まれていないとします。Power Query で紹介したいテクニックがあります。私がそれを初めて見たのは、Imke Feldmann のブログエントリーでした。

このアプローチは、テーブルを昇順で並べ替えることができ、オフセットを必要としないあらゆる状況で非常にうまく機能します。日付フィールドさえも必要ありません。まったく異なるタイプのテーブルである可能性があります。昇順に並べ替えることができる限り、このテクニックを使用できます。

したがって、この Dates Raw テーブルを参照し、これをDatesと呼びます。次に、これを必ず昇順に並べ替えます。

次に、週ごとにグループ化します。その理由は、2 つのインデックス列を追加することになるからです。これらのインデックス列を追加するときは、日単位ではなく週単位にする必要があります。

次に、これらのインデックス列を追加します。ここで重要なことは、追加する最初のインデックスが 0 から始まるインデックスになるということです。次に、1 から始まるインデックスである 2 番目のインデックスを追加します。

次に、この列を取得し、テーブルをそれ自体にマージします。2 つの異なるインデックス列に基づいてそれをマージします。それによって列が移動することになります。

したがって、これを取得して、最初の 0 から始まるインデックスを 1 から始まるインデックスにマージすると、522 行で 521 と一致します。2 番目のテーブルは 1 から始まるため、インデックス 0 を取得しないため、これはまさに私たちが期待していることです。

その後、ISO Weeknumber を使用してこれを適切に展開します。

これにより、前の週の番号が得られます。ここで、元のグループ化を展開し、WeekEnding フィールドと元の ISO 週番号を削除して、重複しないようにします。

これを表にすると、DAX テクニックを使用した場合とまったく同じ結果であることがわかります。これは DAX 測定とまったく同じように機能しますが、この場合、オフセットを使用する必要はありませんでした。

結論

これらは、前の週番号を見つけるための非常に汎用性の高い 2 つの異なる方法です。最初のテクニックでは、そうでなければ難しい計算を非常に簡単に行う際のオフセットの威力がわかります。2 番目の手法は、DAX 測定とまったく同じように機能しますが、この場合、オフセットを使用する必要はありませんでした。

このチュートリアルがお役に立てば幸いです。以下の完全なビデオチュートリアルを視聴し、より類似したコンテンツについては関連リンクをチェックしてください。

乾杯！