コンテンツへスキップ

PowerBIやPower Queryでのカレンダー作成用のクエリー

カレンダー
=ADDCOLUMNS (CALENDAR ("2015/1/1", "2035/12/31"),
"年", YEAR([Date]),
"月", FORMAT ( [Date], "m月" ),
"年月", FORMAT ( [Date], "yyyy年mm月" ),
"月番号", MONTH([Date]),
"曜日番号", WEEKDAY ( [Date], 1),
"週番号", WEEKNUM([Date],1),
"曜日", SWITCH(WEEKDAY([Date],1), 1, "月", 2, "火", 3, "水", 4, "木", 5, "金", 6, "土", 7, "日",""),
"会計年度", IF(MONTH([Date])<4,YEAR([Date])-1,YEAR([Date])),
"四半期", IF(MONTH([Date])<4,"Q4",IF(MONTH([Date])<7,"Q1",IF(MONTH([Date])<10,"Q2","Q3"))),
"半期", IF(MONTH([Date])<4,"下期",IF(MONTH([Date])<10,"上期","下期")),
"半期番号", IF(MONTH([Date])<4,2,IF(MONTH([Date])<10,1,2)),
"日番号", DAY([Date]),"日", FORMAT([Date],"d日"),"YearMonth", FORMAT([Date],"yymm"))