Excel Dax 简明教程

Excel DAX - Time Intelligence

DAX 有一项重要且强大的功能，称为 Time Intelligence 。时间智能让您可以编写引用时间段的 DAX 公式，以便在数据透视表中使用。

DAX has an important and powerful feature, referred to as Time Intelligence. Time intelligence enables you to write DAX formulas that refer to the time periods for use in the PivotTables.

DAX 有 35 个时间智能函数，专门用于随着时间的推移聚合和比较数据。但是，这些 DAX 函数对您需要了解和谨慎使用以避免错误的数据有一些限制。

DAX has 35 time-intelligence functions specifically for aggregating and comparing data over time. However, these DAX functions have some constraints on the data that you need to understand and work with caution to avoid errors.

Why Time Intelligence Makes DAX Powerful?

时间智能函数处理不断变化的数据，具体取决于您在数据透视表和 Power View 可视化中选择的上下文。正如您所知，大多数数据分析涉及按时间段对数据进行汇总，比较跨时间段的数据值，了解趋势并根据未来预测做出决策。

The time intelligence functions work with data that is constantly changing, depending on the context you select in PivotTables and Power View visualizations. As you are aware, most of the data analysis involves summarization of data over time periods, comparing data values across the time periods, understanding the trends and making decisions based on future projections.

例如，您可能希望按产品汇总过去一个月的销售额，并将其与财政年度其他月份的总数进行比较。这意味着，您必须使用日期来按特定时间段对销售交易进行分组和汇总。

For example, you might want to sum sales amounts for the past month product-wise and compare the totals with those of other months in the fiscal year. This means, you have to use the dates as a way to group and aggregate sales transactions for a particular period in time.

这里您可以观察到 DAX 的强大功能。您可以使用 DAX 时间智能函数定义计算字段，从而帮助您随着时间的推移分析数据，而无需更改数据透视表中的日期选择。这使得您的工作更轻松。此外，您可以构建以其他方式无法构建的数据透视表。

This is where you can observe the power of DAX. You can use DAX time intelligence functions to define calculated fields that help you in analyzing the data over time, without having to change the date selections in the pivot tables. This makes your job easier. Moreover, you can build PivotTables that would not be possible any other way.

Requirements for DAX Time Intelligence Functions

DAX 时间智能函数有特定要求。如果未满足这些要求，您可能会收到错误或它们可能无法正常工作。因此，您还可将这些要求称为规则或限制。以下是某些 DAX 时间智能函数要求/规则/限制 −

DAX time intelligence functions have certain requirements. If these requirements are not met, you might get errors or they may not work properly. Hence, you can refer to these requirements as rules or constraints as well. Following are certain DAX time intelligence functions requirements/rules/constraints −

  1. You need to have a date table in your Data Model.

  2. The date table must include a column considered to be the Date column by DAX. You can name the column the way you want, but it should comply with the following conditions: o The date column should contain a contiguous set of dates that covers every day in the time period you are analyzing the data. Every date must exist once and only once in the date column. You cannot skip any dates (For e.g. you cannot skip weekend dates).

  3. DAX time intelligence functions work only on a standard calendar and assume the start of the year as January 1 and the end of the year as December 31, with the months in the year and days in each month as of a calendar year.

但是，您可以为不同的财政年度自定义标准日历。在使用任何时间智能函数之前，验证上述要求是一个好习惯。

However, you can customize a standard calendar for different financial years. It is a good practice to verify the above requirements before any time intelligence function is used.

有关日期表及其在 DAX 公式中使用的更多详细信息，请参阅本教程库中的教程 = DAX 中的数据建模。

For more details on date tables and their usage in DAX formulas, refer to the tutorial = Data Modeling with DAX in this tutorials library.

DAX Time Intelligence Functions – Categories

DAX 时间智能函数可分类如下 −

DAX Time Intelligence functions can be categorized as follows −

  1. DAX functions that return a single date.

  2. DAX functions that return a table of dates.

  3. DAX functions that evaluate expressions over a time period.

DAX Functions That Return a Single Date

此类别的 DAX 函数返回一个日期。

DAX functions in this category return a single date.

此类别有 10 个 DAX 函数 −

There are 10 DAX functions in this category −

Sr.No.

DAX Function & Return Value

1

FIRSTDATE (Date_Column) Returns the first date in the Date_Column in the current context.

2

LASTDATE (Date_Column) Returns the last date in the Date_Column in the current context.

3

FIRSTNONBLANK (Date_Column, Expression) Returns the first date where an expression has a non-blank value.

4

LASTNONBLANK (Date_Column, Expression) Returns the last date where an expression has a non-blank value.

5

STARTOFMONTH (Date_Column) Returns the first date of a month in the current context.

6

ENDOFMONTH (Date_Column) Returns the last date of a month in the current context.

7

STARTOFQUARTER (Date_Column) Returns the first date of a quarter in the current context.

8

ENDOFQUARTER (Date_Column) Returns the last date of a quarter in the current context.

9

STARTOFYEAR (Date_Column, [YE_Date]) Returns the first date of a year in the current context.

10

ENDOFYEAR (Date_Column, [YE_Date]) Returns the last date of a year in the current context.

DAX Functions That Return a Table of Dates

此类别的 DAX 函数返回日期表。这些函数主要用作 DAX 函数 CALCULATE 的 SetFilter 参数。

DAX Functions in this category return a table of dates. These functions will be mostly used as a SetFilter argument to the DAX function - CALCULATE.

此类别有 16 个 DAX 函数。其中 8 个 DAX 函数是“previous”和“next”函数。

There are 16 DAX functions in this category. Eight (8) of these DAX functions are the “previous” and “next” functions.

  1. The “previous” and “next” functions start with a date column in the current context and calculate the previous or next day, month, quarter or year.

  2. The “previous” functions work backward from the first date in the current context and the “next” functions move forward from the last date in the current context.

  3. The “previous” and “next” functions return the resulting dates in the form of a single column table.

Sr.No.

DAX Function & Return Value

1

PREVIOUSDAY (Date_Column) Returns a table that contains a column of all dates representing the day that is previous to the first date in the Date_Column in the current context.

2

NEXTDAY (Date_Column) Returns a table that contains a column of all dates from the next day, based on the first date specified in the Date_Column in the current context.

3

PREVIOUSMONTH (Date_Column) Returns a table that contains a column of all dates from the previous month, based on the first date in the Date_Column in the current context.

4

NEXTMONTH (Date_Column) Returns a table that contains a column of all dates from the next month, based on the first date in the Date_Column in the current context.

5

PREVIOUSQUARTER (Date_Column) Returns a table that contains a column of all dates from the previous quarter, based on the first date in the Date_Column in the current context.

6

NEXTQUARTER (Date_Column) Returns a table that contains a column of all dates in the next quarter, based on the first date specified in the Date_Column in the current context.

7

PREVIOUSYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates from the previous year, given the last date in the Date_Column in the current context.

8

NEXTYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates in the next year, based on the first date in the Date_Column in the current context.

四个 (4) DAX 函数计算一个时期的日期集。这些函数使用当前上下文中的最后日期执行计算。

Four (4) DAX functions calculate a set of dates in a period. These functions perform the calculations using the last date in the current context.

Sr.No.

DAX Function & Return Value

1

DATESMTD (Date_Column) Returns a table that contains a column of the dates for the month to date, in the current context.

2

DATESQTD (Date_Column) Returns a table that contains a column of the dates for the quarter to date, in the current context.

3

DATESYTD (Date_Column, [YE_Date]) Returns a table that contains a column of the dates for the year to date, in the current context.

4

SAMEPERIODLASTYEAR (Date_Column) Returns a table that contains a column of dates shifted one year back in time from the dates in the specified Date_Column, in the current context. Note− SAMEPERIODLASTYEAR requires that the current context contains a contiguous set of dates. If the current context is not a contiguous set of dates, then SAMEPERIODLASTYEAR will return an error.

  1. Four (4) DAX functions are used to shift from the set of dates that are in the current context to a new set of dates. These DAX functions are more powerful than the previous ones. DAX functions – DATEADD, DATESINPERIOD and PARALLELPERIOD shift some number of time intervals from the current context. The interval can be day, month, quarter or year, represented by the key words – DAY, MONTH, QUARTER and YEAR respectively. For example:

  2. Shift backward by 2 days.

  3. Move forward by 5 months.

  4. Move forward by one month from today.

  5. Go back to same quarter in the last year. If the function argument - number of intervals (integer value) is positive, shift is forward and if it is negative, shift is backward. DAX function – DATESBETWEEN calculates the set of dates between the specified start date and the end date.

Sr.No.

DAX Function & Return Value

1

DATEADD (Date_Column, Number_of_Intervals, Interval) Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

2

DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

3

PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) Returns a table that contains a column of dates that represents a period parallel to the dates in the specified Date_Column in the current context, with the dates shifted a number of intervals either forward or backward in time.

4

DATESBETWEEN (Date_Column, Start_Date, End_Date) Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DAX Functions that Evaluate Expressions Over a Time Period

此类别中的 DAX 函数在指定的时间段内对表达式求值。

DAX Functions in this category evaluate an expression over a specified time period.

此类别中有九 (9) 个 DAX 函数 −

There are nine (9) DAX functions in this category −

  1. Three (3) DAX functions in this category can be used to evaluate any given expression over a specified time period.

Sr.No.

DAX Function & Return Value

1

TOTALMTD (Expression, Date_Column, [SetFilter]) Evaluates the value of the expression for the dates in the month to date, in the current context.

2

TOTALQTD (Expression, Date_Column, [SetFilter]) Evaluates the value of the expression for the dates in the quarter to date, in the current context.

3

TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the value of the expression for the dates in the year to date, in the current context

  1. Six (6) DAX functions in this category can be used to calculate the opening and the closing balances. The opening balance for any period is the same as the closing balance for the previous period. The closing balance includes all data through the end of the period, while the opening balance does not include any data from within the current period. These DAX functions always return the value of an expression evaluated for a specific point in time.

  2. The point in time we care about is always the last possible date value in a calendar period.

  3. The opening balance is based on the last date of the previous period, while the closing balance is based on the last date in the current period.

  4. The current period is always determined by the last date in the current date context.

Sr.No.

DAX Function & Return Value

1

OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Evaluates the expression at the first date of the month in the current context.

2

CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Evaluates the expression at the last date of the month in the current context.

3

OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Evaluates the expression at the first date of the quarter, in the current context.

4

CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Evaluates the expression at the last date of the quarter in the current context.

5

OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the expression at the first date of the year in the current context.

6

CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the expression at the last date of the year in the current context.