例文を使って繰り返し業務を時短

【Power BI】DAXでカレンダーテーブルを作成

Power BIのDAXでカレンダーテーブルを作成する方法をご紹介 Power BI

カレンダーテーブルのDAX文


日付テーブル = 
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)
    )

Power Queryの場合

ホーム＞データの入力＞（何も入力せず）テーブルの作成＞DAX文を入力

Power BIの場合

モデリング＞新しいテーブル

※DAX文をコピペした後、データ型を定義する

参考

Microsoft)[Power BI] DAX入門(2) カレンダーテーブルの作成
https://docs.microsoft.com/ja-jp/archive/blogs/dataplatjp/dax2

カレンダーテーブルのDAX文　別パターン

日付テーブル = 
VAR StartDate=TODAY()-365*5//現在から5年前まで
VAR EndDate=TODAY()+365*5//現在から5年後まで
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" ),
    "年度会計",if( MONTH ([Date])<4,YEAR ( [Date] )-1,YEAR ( [Date] )),
    "月並べ替え用",SWITCH(MONTH(2024/07/27),1,10,2,11,3,12,4,1,5,2,6,3,7,4,8,5,9,6,10,7,11,8,12,9),
    "上期_下期",if(MONTH([Date])<4,"下期",if(MONTH([Date])<7,"上期",if(MONTH([Date])<10,"上期","下期"))),
    "上期_下期並び替え",if(MONTH([Date])<4,2,if(MONTH([Date])<7,1,if(MONTH([Date])<10,1,2)))
　　"相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY)    
)

解説

  1. 「モデリング」タブをクリックし、「新しいテーブル」を選択します。
  2. 「DAX式」ボックスに、以下のDAX式を入力します。
  3. 「名前」ボックスに「Calendar」と入力し、「Enter」キーを押します。
  4. テーブルが正しく作成されたことを確認するために、Power BIの「データ」ビューに移動し、「Calendar」テーブルを選択します。テーブルには、指定された期間の各日付に関する情報が表示されます。

＊これで、DAXを使用してPower BIでカレンダーテーブルを作成する方法がわかりました。このテーブルは、時系列分析やレポート作成に役立ちます。

M言語（Power　Query）

このPower QueryのM言語は、日付を含むリストを作成し、各日付から様々な日付情報を抽出するためのものです

let
  // 今日の日付を設定し、その日付の年度を計算する。
  today = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 9)),
  ThisFY = if Date.Month(today) > 3 then Date.Year(today) else Date.Year(today)-1,
 
  // 変数を設定した場合
  // List.Dates(StartDate, Duration.Days(EndDate - StartDate), #duration(1, 0, 0, 0))
  source = List.Dates(#date(2020, 12, 31), 1000, #duration(1, 0, 0, 0)),

  convert_table = Table.FromList(source, Splitter.SplitByNothing(),{"Date"},ExtraValues.Error),
  Changed_Type = Table.TransformColumnTypes(convert_table, {{"Date", type date}}),
  add_year_column = Table.AddColumn(Changed_Type, "年", each Date.Year([Date]), Int64.Type),
  add_month_column = Table.AddColumn(add_year_column, "月", each Date.Month([Date]), Int64.Type),
  add_day_column = Table.AddColumn(add_month_column, "日", each Date.Day([Date]), Int64.Type),
  // https://docs.microsoft.com/ja-jp/powerquery-m/expressions-values-and-let-expression
  add_年度_column = Table.AddColumn(add_day_column, "年度", each if Date.Month([Date]) > 3
        then Date.Year([Date])
        else Date.Year([Date])-1, Int64.Type),
  add_年度四半期_数値_column = Table.AddColumn(add_年度_column, "年度四半期", each if Date.Month([Date]) < 4
        then 4
        else if Date.Month([Date]) < 7
        then 1
        else if Date.Month([Date]) < 10
        then 2
        else 3, Int64.Type),
  add_年度四半期_0Q_column = Table.AddColumn(add_年度四半期_数値_column, "年度四半期_0Q", each if Date.Month([Date]) < 4
        then "4Q"
        else if Date.Month([Date]) < 7
        then "1Q"
        else if Date.Month([Date]) < 10
        then "2Q"
        else "3Q", type text),
 
  add_週_数値_column = Table.AddColumn(add_年度四半期_0Q_column, "週", each Date.WeekOfYear([Date], 1), Int64.Type),

  // 月曜日=> 0 , 火曜日=> 1...
  add_曜日_数値_column = Table.AddColumn(add_週_数値_column, "曜日", each Date.DayOfWeek([Date], 1), Int64.Type),
  add_曜日名_column = Table.AddColumn(add_曜日_数値_column, "曜日名", each Date.DayOfWeekName([Date], "ja-JP"), type text),
  add_月名_column = Table.AddColumn(add_曜日名_column, "月名", each Date.MonthName([Date]), type text),
  add_年月_column = Table.TransformColumnTypes(Table.AddColumn(add_月名_column, "add_年月_column", each Text.Combine({Text.From([年], "ja"), "年", [月名]})), {{"add_年月_column", type text}}),
  
  add_相対日付_column = Table.AddColumn(add_年月_column, "相対日付", each Duration.Days(
            [Date] - today
        ), Int64.Type),
 
  add_相対月_column = Table.AddColumn(add_相対日付_column, "相対月", each 12 * ( [年] - Date.Year(today) ) + [月] - Date.Month(today), Int64.Type),
  add_相対年度_column = Table.AddColumn(add_相対月_column, "相対年度", each [年度] - ThisFY, Int64.Type),
  add_相対年_column = Table.AddColumn(add_相対年度_column, "相対年", each [年] - Date.Year(today), Int64.Type),
  add_会計期_column = Table.AddColumn(add_相対年_column, "会計期", each Date.Year(Date.AddMonths([Date],-5)) - 1940, Int64.Type),
  add_相対会計期_column = Table.AddColumn(add_会計期_column, "相対会計期", each Date.Year(Date.AddMonths([Date],-5)) - Date.Year(Date.AddMonths(today,-5)), Int64.Type)
in
  add_相対会計期_column

解説

  1. 今日の日付を設定し、その日付の年度を計算する。
    • 現在の日付（UTC時間）を取得し、日本標準時（+9時間）に変換して、その日付の年、月、日をDateTime.Date関数を使用して抽出しています。次に、今日の日付の月が4よりも大きい場合、その年をThisFY変数に代入し、そうでなければ、ThisFY変数に現在の年から1を引いた値を代入しています。これにより、現在の日付が属する年度が取得されます。
      • DateTimeZone.UtcNow()によりUTCの現在時刻を取得し、DateTimeZone.SwitchZone()により9時間足した日本時間に変換しています。DateTime.Date()により日付のみを抽出して、変数todayに設定しています。
      • 変数todayで設定した現在日付の月が4以上（4月から12月）の場合、年度は当年となります。月が3以下（1月から3月）の場合、年度は前年となります。この判定をif文で行っています。
  2. 日付のリストを作成する。
    • このステップでは、List.Dates関数を使用して、指定された開始日から指定された日数分の日付を含むリストを作成しています。この場合、開始日は2020年12月31日、日数は1000日（約2.7年）、期間は1日（#duration(1, 0, 0, 0)）です。
      • List.Dates()を使用して、開始日、期間、インターバルを指定して日付のリストを作成しています。#date()で開始日を指定し、#duration()で期間（1000日）、インターバル（1日）を指定しています。
  3. 日付情報を含むテーブルを作成する。
    • このステップでは、Table.FromList関数を使用して、日付を含むリストをテーブルに変換しています。次に、Table.TransformColumnTypes関数を使用して、日付列のデータ型をdateに変更しています。
  4. 日付から年、月、日を抽出する。
    • このステップでは、Table.AddColumn関数を使用して、年、月、日の列をテーブルに追加しています。これらの列には、各日付から抽出された年、月、日が含まれています。
  5. 日付から年度、四半期、週、曜日、月名を抽出する。

コメント

タイトルとURLをコピーしました