Powre BIやExcelでDAXを使用してタイムインテリジェンスを使用する場合は、必ず日付テーブル（カレンダーテーブル）が必要となります。Power Queryを使って、簡単に日付テーブルを作成してみます。
日付テーブルは、以下の条件を満たす必要があります。
- "日付列" と呼ばれる日付型の列が必要です。
- 日付列には一意の値が含まれている必要があります。
- 日付列に空白を含めることはできません。
- 日付列に欠落している日付があってはなりません。
- 日付列は年間全体にわたっている必要があります。 1 年は必ずしも暦年 (1 月から 12 月) ではありません。
- 日付テーブルには日付テーブルとしてマークされている必要があります。
この中で、5番目の条件を満たすために、1月1日から12月31日までを指定して作成してやる必要があります。会計年度が1月スタートでない場合は、Power Queryには会計年度を使って計算する関数がないため、開始日と終了日を自動で取得するために工夫が必要となります。
使用するのは、ファクトテーブル 「FactTable」 の日付項目 「Date」 を使います。
会計年度の開始月の設定
会計年度が始まる月をパラメータに設定します。
4月からスタートする場合は、4を設定します。
会計年度を算出する関数を作成する
日付から会計年度を算出する関数を作成します。
GetFY
(CurrentDate as date) as number =>
Date.Year(
Date.AddMonths(
CurrentDate,
(FYStartMonth - 1) * -1
)
)
ここは、普通、if とか使うのがアリガチなのですが、私のこだわりにより計算で出します。
ちなみに、敢えてifを使うと以下のような感じになります。
GetFY_if使用
(CurrentDate as date) as number =>
if
Date.Month(CurrentDate) < FYStartMonth
then
Date.Year(CurrentDate) - 1
else
Date.Year(CurrentDate)
開始日と終了日
前の関数を使って開始日と終了日を計算します。
StartDate
let
Source = FactTabe,
// ファクトテーブルの最小日付
MinDate =
List.Min(
Source[Date]
),
// 最小日付の年度を算出
MinFY = GetFY(MinDate),
// 年度の最初の日付
StartOfFY =
#date(
MinFY,
FYStartMonth,
1
)
in
StartOfFY
EndDate
let
Source = FactTabe,
// ファクトテーブルの最大日付
MaxDate =
List.Max(
Source[Date]
),
// 翌年度の最初の日付から1日前の日付
EndOfFY =
Date.AddDays(
#date(
GetFY(MaxDate) + 1,
FYStartMonth,
1
),
-1
)
in
EndOfFY
日付テーブルの日付項目
開始日と終了日を使用して、 List.Dates で日付テーブルの日付項目を作成します。
CalendarTable
let
Source = List.Dates(
StartDate_func,
Duration.Days(EndDate - StartDate) + 1,
#duration(1,0,0,0)
)
in
Source
これだけ書いておけば、以下のようなリストが出来上がります。
このリストから、テーブルに変換し、日付型に設定します。その後、年、月、日の列を追加していきます。
会計年度の項目を追加
会計年度は、先程の関数を使用します。
四半期の項目を追加
四半期の番号を追加します。
まず、関数を作成します。
GetQ
(CurrentDate as date) as number =>
Number.RoundUp(
Date.Month(
Date.AddMonths(
CurrentDate,
(FYStartMonth - 1) * -1
)
) / 3,
0
)
年度の追加と同様に、「列の追加」タブで、「カスタム関数の呼び出し」を選択します。
以上で、以下のような日付テーブルが出来上がります。
