拡張日付テーブルでのオフセット関数の使用

LuckyTemplates でオフセット関数を使用する方法を説明します。これは、特に日付テーブルで使用できる最も動的なツールの 1 つです。

タイム インテリジェンス プロジェクトにそれを適用する方法をマスターすると、他のアプローチでは得られない、より詳細な洞察を得ることができます。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

目次

• オフセット関数の仕組みを理解する
• 週オフセット列の例
• DAX でのオフセットの使用
• 特定の週のオフセット
• 結論

オフセット関数の仕組みを理解する

まず、オフセット関数の仕組みを説明します。

オフセットは、今日の日付とデータ内の他の日付との差を示します。

以下の日付表からわかるように、実際のデータの動きを確認できるように、四半期と年に焦点を当てました。

紫色の行は現在の期間を示します。現在の期間のオフセット値は常に 0 であることに注意してください。

その上の黄色は、過去のすべてのデータを示しています。ここでの数値は 0 または 0 未満であり、負のデータで表されていることがわかります。

これは、将来のデータでは正の値が示されることを意味します。列の粒度によっては、0 が含まれる場合もあります。

では、何がオフセットを強力にするのでしょうか?

オフセットは、テーブル内の異なるデータ間の差異を表す連続番号です。しかし、オフセットが優れているのは、境界がないことです。

たとえば、四半期は 1 から 4 までで構成され、1 年の 4 つの四半期を表します。月は 1 から 12 まで続き、週は年に応じて 1 から 52 または 53 まで続きます。ただし、最後の番号に到達すると、最初からやり直す必要があります。

オフセットの場合はこの限りではありません。オフセットに関しては、常に現在の日付と参照されている他の日付に対する相対位置が表示されます。

したがって、日付テーブルが更新されるたびに、オフセットを機能させるロジックも更新されます。これは、更新された値が日付テーブルにも表示されることを意味します。

週オフセット列の例

オフセット関数がどのように機能するかをより明確に理解するために、それがどのように適用されるかの例を示します。

私のサンプル データでは、今日の日付が2020 年 4 月 24 日として表示されます。

繰り返しになりますが、オフセットの原則では、現在の期間は 0 で表され、将来のデータは正の値で表され、過去の期間は負の数で表されると規定されています。

ページには WeekOffset 値を含むスライサーもあります。現在、-53 から 0 までのすべての値が表示され、0 は今週を表します。

4 月 24 日 (今日) は第 17 週にあたります。

この拡張日付テーブルはISO 週番号のみをカバーしています。これは、週が月曜日から始まることを意味します。これは、1 週間には常に 7 日が含まれることも意味します。

しかし、同じロジックに従わないカスタムウィーク要件もたくさんあります。これらのケースについて詳しくは、LuckyTemplates フォーラムで取り上げられているさまざまなトピックをご覧ください。

また、状況に適している場合は、週と年ではなく月と年を使用することも検討できます。

サンプル データに戻ると、WeekOffset値が完全な連続番号の範囲内にあることに気づいたかもしれません。

これは、それらが全体を通して同じ粒度を共有しているためです。重複する値や欠損値はありません。

もちろん、ここで使用されるTotal Salesメジャーは通常の方法で行われ、 QuantityとPrice を掛けたSalesに対する通常のを使用するだけです。次に、すべてが集計されて週次の値が取得されます。

しかし、過去 4 週間の売上だけを確認したい場合はどうすればよいでしょうか?

スライサーを-3 から 0の範囲に変更するだけです。

さて、私の表には過去 4 週間の売上のみが表示されています。

したがって、時間が経つにつれて、日付テーブルが更新されます。新しい販売結果がファクト テーブルにロードされた場合にも、同じことが起こります。

DAX でのオフセットの使用

今回はDAX対策でのoffset関数の使い方を紹介します。

週と年と総売上高を含むテーブルがあります。

したがって、オフセットなしで前週の売上を計算したい場合は、まず CurrWeek の数値を抽出し、次にCurr Year の値を確認する必要があります。

年の境界を越えてスキップする必要がある場合は、昨年の Weeknumberが何であるかを計算する必要があります。

そこから、 SUMX を日付テーブルの反復関数として使用します。また、 CurrWeek が週番号 1であるかどうかも確認する必要があります。

第 1 週でない場合は、Curryear の CurrWeek 値から 1 を減算するだけです。その後、最終的に総売上高を合計することができます。

以下の表を見ると、すべての値が完全に集計されていることがわかります。[総売上高] に表示される値は、次の週に単純に引き継がれます。

2017 年から 2018 年に年の境界を越えても、パターンは崩れません。2017 年の最終週の総売上高は、2018 年の第 1 週でも完全に計算されます。

今回は、日付テーブルのオフセットを適用して、同じ計算を実行します。

オフセットを使用すると、このメジャーは次のようになります。

したがって、PrevWeek値を取得するには、選択したWeekOffset を参照して 1 を減算するだけです。

関数を使用して、WeekOffsetがPrevWeek値と等しい日付の合計売上を計算します。

結果を見ると、2017年から2018年への移行期においても、結果は完璧に見えます。

ただし、問題は合計にあります。明らかに、これは正しい値ではありません。

これを修正する方法を説明します。

まず、「週と年」列が昇順であることに注意してください。

これを変更して、列を2020 を先頭にして降順に並べ替えます。

Week & Yearからのコンテキストがないため、現在の週のオフセット値は識別されませんが、それでも 1 が減算されます。しかし、私にできることは、累計を適用して合計を調整することです。

メジャーは次のようになります。

小節の始まりは変わりません。これは、以前に適用した対策とまったく同じです。

日付の FirstWeek に対してCALCULATE関数を使用し、そこから 1 を減算する必要があります。

また、 ALLSELECTED日付からMAX WeekOffset値を特定し、そこから 1 を減算する必要もあります。

次に、 Total Salesに対してCALCULATE関数を再度使用します。関数を使用して、すべての日付を含めます。WeekOffset 値は、 FirstWeek 以上、 LastWeek 以下である必要があります。

繰り返しますが、これは累積合計の基本パターンに従います。

パターンを使用して総計の確認に進みます。

したがって、週と年が ISINSCOPEの場合、結果にはLWSalesが表示されると予想します。しかし、そうでない場合は、LWTotalが表示されると思います。

したがって、テーブルに戻ると、正しい合計が表示されるようになります。

特定の週のオフセット

前に使用した、データをフィルターして過去 4 週間のみを表示した例に戻りましょう。

測定に入ると、オフセットを使用して同じ結果を得ることができます。それは次のようになります。

必要なのは、行から得られるコンテキストの現在のWeekOffset値を特定することだけでした。

これを取得したら、WeekOffset がThisWeekから 3 を引いた値以上、または ThisWeek の値以下であるすべての日付の合計売上高にCALCULATE関数を適用できます。

これは、複製しようとしている例とまったく同じ値を返します。

LuckyTemplates での月次平均結果に基づく累積合計
LuckyTemplates での異なる日の結果のデータ正規化
LuckyTemplates で包括的な日付テーブルを非常に高速に構築

結論

上で示した例は、動的オフセットが実際にどのようなものであるかを証明しています。これらはフィルター、スライス、さらには DAX 計算でも使用できます。

オフセットは、週、月、四半期、年、会計年度の拡張日付テーブルでも使用できます。柔軟性が高く、望む結果を簡単に得ることができます。

オフセットを試し続けて、他にどのような用途があるかを確認してください。またオフセット関数が役立つことが判明した他のケースを確認することもできます。

ではごきげんよう、

メリッサ