More than 1 year has passed since last update.

雑・Excel入門試論 - 脱VLOOKUPの思考Advent Calendar 2022

@spumoni(spumoni)

雑・Excel入門試論 - 脱VLOOKUPの思考 23 DAX - タイムインテリジェンス

Last updated at 2023-01-03Posted at 2022-12-22

タイムインテリジェンスとは

　タイムインテリジェンス関数は、時間軸を伴う計算を行います。月ごとや年ごとの集計や、前月比、前年比などの計算ができます。

　DAXでのタイムインテリジェンスとは、日付フィルターのフィルターコンテキストを変更する計算です。この計算を行うためには、日付テーブルを作成し、日付テーブルとしてマーク、リレーションの作成が必要です。

※日付テーブルの作成については「雑・Excel入門試論 - 脱VLOOKUPの思考 20 パワーピボット - 日付テーブル」参照

期間の計算

関数名	内容
DATESYTD	年度累計の日付を含む単一テーブル
DATESMTD	月累計の日付を含む単一テーブル
DATESQTD	四半期累計の日付を含む単一テーブル
TOTALYTD	年累計
TOTALMTD	月累計
TOTALQTD	四半期累計
DATESBETWEEN	指定した開始日で始まり、指定した終了日までの日付の列を含むテーブル
DATESINPERIOD	指定した開始日で始まり、指定した終了日まで、指定した間隔の日付の列を含むテーブル

TOTALYTD

TOTALYTD構文

TOTALYTD(
    <expression>,       // スカラー値を返す式
    <dates>             // 日付を含む列
    [,<filter>]         // 現在のコンテキストに適用するフィルター
    [,<year_end_date>]  // 年度末の日付
)

　6月30日が年度末となる年累計を計算します。

TotalYtd

TotalYtd := TOTALYTD(
	SUM(financials[Sales]),  // スカラー値を返す式
	DateTable[Date],         // 日付を含む列
	ALL(DateTable),          // 現在のコンテキストに適用するフィルター
	"6/30"                   // 年度末の日付
)

　TotalYtdは、7月から6月までが累計される計算になっています。

期間の比較

　タイムインテリジェンス関数のもう一つのグループは、時間軸をシフトすることに関するものです。

関数名	内容
DATEADD	指定した間隔だけシフトした日付のテーブル
PARALLELPERIOD	指定した日付列と並行する期間をシフトさせた日付のテーブル
SAMEPERIODLASTYEAR	1年前にシフトした日付のテーブル
NEXTDAY	翌日の日付のテーブル
NEXTMONTH	翌月の日付のテーブル
NEXTQUARTER	翌四半期の日付のテーブル
NEXTYEAR	翌年の日付のテーブル
PREVIOUSDAY	前日の日付のテーブル
PREVIOUSMONTH	前月の日付のテーブル
PREVIOUSQUARTER	前四半期の日付のテーブル
PREVIOUSYEAR	前年の日付のテーブル

DATEADD と PARALLELPERIOD

DATEADD構文

DATEADD(
    <dates>,                // 日付を含む列
    <number_of_intervals>,  // 日付に対する間隔を指定する整数
    <interval>)             // 日付をシフトする間隔 YEAR, QUARTER, MONTH, DAYのいずれか

PARALLELPERIOD構文

PARALLELPERIOD(
    <dates>,               // 日付を含む列
    <number_of_intervals>, // 日付に対する間隔を指定する整数
    <interval>             // 日付をシフトする間隔 YEAR, QUARTER, MONTH, DAYのいずれか
)

　DATEADDとPARALLELPERIODは、同じ引数をとり、同じ動きをするように見えます。

DateAdd

DateAdd := CALCULATE(
    SUM(financials[Sales]),
    DATEADD(DateTable[Date],-1,YEAR)
)

ParallelPeriod

ParallelPeriod := CALCULATE(
    SUM(financials[Sales]),
    PARALLELPERIOD(DateTable[Date],-1,YEAR)
)

　しかし、ここに月を表示を加えると、以下のようになります。

　つまり、DATEADD では、1年前の日付を返すのに対して、 PARALLELPERIOD は、前年の全ての日付を返しています。

PREVIOUSMONTH

PREVIOUSMONTH構文

PREVIOUSMONTH(<Dates>)

　前月分の金額が表示されます。

PREVIOUSMONTH例

=CALCULATE(
    SUM(financials[Sales]),
    PREVIOUSMONTH(DateTable[Date])
)

PREVIOUSMONTH で返されるのは、前月の日付のテーブルなので、先程の表に日付を加えると、すべての日付に翌月の合計金額が表示されます。

タイムインテリジェンスで使わない関数

　以下の関数は、タイムインテリジェンス関数ではありません。

関数名	構文	説明
DATE	`DATE(<year>, <month>, <day>)`	指定された日付をdatetime形式で返す
TIME	`TIME(hour, minute, second)`	指定された時間をdatetime形式で返す
DATEVALUE	`DATEVALUE(date_text)`	テキスト形式の日付をdatetime形式に変換
TIMEVALUE	`TIMEVALUE(time_text`)	テキスト形式の時刻をdatetime形式に変換
YEAR	`YEAR(<date>)`	日付の年を4桁の整数で返す
MONTH	`MONTH(<datetime>)`	月を数値として返す
DAY	`DAY(<date>)`	月の日付を返す
WEEKDAY	`WEEKDAY(<date>, <return_type>)`	日付の曜日を返す（※１）
WEEKNUM	`WEEKNUM(<date>[, <return_type>])`	日付の週番号を返す（※２）
HOUR	`HOUR(<datetime>)`	時間を０から２３の数値で返す
MINUTE	`MINUTE(<datetime>)`	分を０から５９の数値で返す
SECOND	`SECOND(<time>)`	病を０から５９の数値で返す
NOW	`NOW()`	現在の日時を返す（※３）
TODAY	`TODAY()`	現在の日付を返す（時間は0:00）
EDATE	`EDATE(<start_date>, <months>)`	開始日から指定された月数の日付をdatetime形式で返す（※４）
EOMONTH	`EOMONTH(<start_date>, <months>)`	指定された月数の月の最終日をdatetime形式で返す
YEARFRAC	`YEARFRAC(<start_date>, <end_date>, <basis>)`	2つの日付の間の全日数で表される年の端数を計算します（※５）

※１ return_type

1 : 日曜日１から土曜日７
2 : 月曜日１から日曜日７
3 : 月曜日０から日曜日６

※２
　規定では、１月１日が含まれる週を都市の最初の週とみなします。
　return_typeが21の時は、新しい年の日付が４日以上含まれる週が最初の週となります。(ISO 8601)

return_type	週の始まり
1(規定)	日曜日
2	月曜日
11	月曜日
12	火曜日
13	水曜日
14	木曜日
15	金曜日
16	土曜日
17	日曜日
21	月曜日

※３
　エクセルの計算式で使用するNOW()関数は、式があるワークシートと別のワークシート上だけでなく、同時に開かれている別のブックのワークシート上であっても計算が行われると、NOW()関数は必ず再計算が行われます。
　しかし、DAXのNOW()関数は、継続的な更新は行われず、数式を含む列が更新されたときにのみ変更されます。

※４
　monthsの小数点以下は切り捨てられます。
　start_dateがテキストの場合は、PCのロケールと日付時刻設定を使用してテキストを日付に変換します。
　start_dateの日付が該当する月の最終日より後の場合は、その月の最終日が返されます。例えば、EDATE("2023/1/31", 1)は、2023年2月28日を返します。

※５

basis	説明
0(規定)	US(NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

コンテンツ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up