[ Power BI ] カレンダーテーブルに会計期を追加する。

やりたいこと

勤めている会社は5月末決算で6月始まりの会計期です。また、売上報告では〇〇年ではなく、 「第〇〇期」 みたいな表記を多用します。レポート作成時には年度や、西暦区切りでは売上管理はしにくいため、会計期をカレンダーテーブルに併記する必要がでてきました。

カレンダーテーブルとは

超大事。

まずはカレンダーテーブルをつくる。コピペで。

神々たちのエントリーを参考にDAXでカレンダーテーブルを作成（コピペ）します。

日付テーブル = 
VAR StartDate=DATE(2000,01,01)
VAR EndDate=DATE(2030,12,31)
VAR Today=UTCTODAY()+9/24
VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
    BaseCalendar,
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "週_数値", WEEKNUM ( [Date], 1 ),
    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),
    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),
    "曜日_aaa", FORMAT([Date],"aaa"),
    "日と曜日", FORMAT ( [Date], "DDaaa" ),
    "相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY)
    )

VAR StartDate=＊＊＊＊＊＊ VAR EndDate=＊＊＊＊＊＊ のところは、任意の日付を入れてもOKですし、例えば売上テーブルの売上日時列を指定してあげることで、動的に定義することも可能です。

DAX式を書く

まずは会計期のDAX

"会計期",YEAR(EDATE ( [Date] , -5 ) ) - 1940

5月末決算なので、 EDATE() を使用して現在の日付コンテキストから5ヶ月巻き戻し、YEAR() で囲ってあげることで、会計期がスタートした西暦年を取得します。その上で、現在の会計期に合うように数字を引いてあげるという考え方になります。

2022年1月7日段階で81期になるので、この場合は -1940 としてあげると、

最終的に、 2021 -1940 = 81

となり 81期 が出力されます。

参考までに → EDATE()

次に相対会計期のDAX

前期比のレポートを作成するときなど、相対会計期が入っていると便利ですよね。
売上のグラフを作成して、相対会計期 0 を指定しておけば、常に現在日付に対応した会計期が表示されます。フィルターパネルで ○日以降 とか指定しなくてもOKです。

繰り返しになりますが、5月末決算、6月開始の会計年度をもっている会社の場合で考えます。
相対会計期は現在会計期を0とします。

日付コンテキストをもとに出した会計期から、
現在日付（冒頭にtoday変数で定義されている）の会計期を引けばOKです。

"相対会計期",YEAR(EDATE([Date],-5)) - YEAR(EDATE(today,-5))

完成

ちょうど5/31で会計期、相対会計期が入れ替わっていますね。

コピペ用に完全版も貼り付けます。

日付テーブル = 
VAR StartDate=DATE(2000,01,01)
VAR EndDate=DATE(2030,12,31)
VAR Today=UTCTODAY()+9/24
VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
    BaseCalendar,
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "週_数値", WEEKNUM ( [Date], 1 ),
    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),
    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),
    "曜日_aaa", FORMAT([Date],"aaa"),
    "日と曜日", FORMAT ( [Date], "DDaaa" ),
    "相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY),
    "会計期",YEAR(EDATE([Date],-5)) - 1940,
    "相対会計期",YEAR(EDATE([Date],-5)) - YEAR(EDATE(today,-5))
    )

自社用にカスタマイズ

• 会計期の -1940
• 相対会計期の -5

としているところ調整すれば自社用にカスタマイズできます。
例えば、現在30期目で、10月末決算だとすれば下記のようになります。
（2022年1月7日現在）

"会計期",YEAR(EDATE ( [Date] , -5 ) ) - 1992
"相対会計期",YEAR(EDATE([Date],-10)) - YEAR(EDATE(today,-10))

勉強する

カレンダーテーブル作成用のDAXには日付と時刻関数のエッセンスが詰まっているから、
各カラムがどのように成り立っているのか説明できるようにしよう。