【Power BI備忘録】日付テーブル作成のDAX関数

 Power BIで時系列データを扱う場合は、日付テーブルをDAX関数で作成すると便利になるようです。

一意で、連続している日付列を持ったテーブルがあると、タイムインテリジェンス関数の利用などが可能になるようです。

ネット上でいろいろな日付テーブル作成のDAX関数が紹介されているので参考にしていきたいと思います。

下記は、ネット上にあるDAX関数です。変数やCALENDARAUTO()を使うもの、使わないものなどがあります。

MAX([データが持っている日付列])とMIN([データが持っている日付列])のデータを使って日付テーブルを作成するのがいいかもしれません。この場合は下記のような形になるようです。

CalendarTable 　= 　CALENDAR（MIN([データが持っている日付列]),MAX([データが持っている日付列])）

この場合、範囲の終わりの方を「EOMONTH(MAX([データが持っている日付列]), 0))」とすれば、該当月の月末までの日付が生成されます。

作成した日付テーブルは、ファクトテーブルとの間にリレーションシップを作成して利用します。この場合、日付テーブルを「日付テーブルとしてマークする」という設定にするといいようです。

「日付テーブルとしてマークする」の設定をしていないとタイムインテリジェンス関数がうまく機能しないことがあるようです。ただし、日付型の日付列でリレーションしていれば「日付テーブルとしてマークする」は必須でもないようです。

--------------------------------------------------

CalendarTable = 

var BaseCalendar = CALENDARAUTO()

RETURN

    GENERATE(

        BaseCalendar,

        var BaseDate = [Date]

        var YearNumber = YEAR(BaseDate)

        var MonthNumber = MONTH(BaseDate)

        var DateNumber = DAY(BaseDate)

        var DayNumber = WEEKDAY(BaseDate, 1)

        RETURN 

        ROW(

            "年_番号", YearNumber,

            "年", FORMAT(BaseDate, "yyyy年"),

            "月_番号", MonthNumber,

            "月", FORMAT(BaseDate, "mm月"),

            "年月_番号", FORMAT(BaseDate, "yyyymm"),

            "年月", FORMAT(BaseDate, "yyyy年mm月"),

            "日_番号", DateNumber,

            "日", FORMAT(BaseDate, "dd日"),

            "曜日_番号", DayNumber,

            "曜日", SWITCH(DayNumber, 1, "日", 2, "月", 3, "火", 4, "水", 5, "木", 6, "金", 7, "土",""),

            "相対月数", DATEDIFF(TODAY(), BaseDate, MONTH)

        )

    )

---------------------------------------------------

Calendar = 

ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

---------------------------------------------------

---------------------------------------------------

日付テーブル = 

VAR StartDate="2016/04/01"

VAR EndDate="2020/3/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)

    )

---------------------------------------------------

https://docs.microsoft.com/ja-jp/learn/modules/dax-power-bi-add-calculated-tables/1-introduction