Wednesday, May 27, 2020

Time intelligence in power pivot in excel

Data Analysis Expressions (DAX) has 35 functions specifically for aggregating and comparing data over time. Unlike DAX's date and time functions, Time-intelligence functions don't really have anything similar in Excel. This is because time-intelligence functions work with data that is constantly changing, depending on the context you select in PivotTables and Power View visualizations.

In order to work with time-intelligence functions, you need to have a date table included in your Data Model. The date table must include a column with one row for every day of each year included in your data. This column is considered to be the Date column (although it can be named whatever you like). Many time-intelligence functions require the date column in order to calculate according to the dates you select as fields in a report. For example, if you have a measure that calculates a closing quarter-end balance by using the CLOSINGBALANCEQTR function, in order for Power Pivot to know when the end of the quarter really is, it must reference the date column in the date table to know when the quarter starts and ends. To learn more about date tables, take a look at Understand and create date tables in Power Pivot in Excel.

Functions

Functions that return a single date

Functions in this category return a single date. The result can then be used as arguments to other functions.

The first two functions in this category return the first or last date in the Date_Column in the current context. This can be useful when you want to find the first or last date on which you had a transaction of a particular type. These functions take only one argument, the name of the date column in your date table.

The next two functions in this category find the first or last date (or any other column value as well) where an expression has a non-blank value. This is most often used in situations like inventory, where you want to get the last inventory amount, and you don't know when the last inventory was taken.

Six more functions that return a single date are the functions that return the first or last date of a month, quarter, or year within the current context of the calculation.

Functions that return a table of dates

There are sixteen time-intelligence functions that return a table of dates. Most often, these functions will be used as a SetFilter argument to the CALCULATE function. Just like all time-intelligence functions in DAX, each function takes a date column as one of its arguments.

The first eight functions in this category start with a date column in a current context. For example, if using a measure in a PivotTable, there might be a month or year on either the column labels or row labels. The net effect is the date column is filtered to include only the dates for the current context. Starting from that current context, these eight functions then calculate the previous (or next) day, month, quarter, or year and return those dates in the form of a single column table. 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.

The next four functions in this category are similar, but instead of calculating a previous (or next) period, they calculate the set of dates in the period that is "month-to-date" (or quarter-to-date, or year-to-date, or in the same period of the previous year). These functions all perform their calculations using the last date in the current context. Note that SAMEPERIODLASTYEAR requires that the current context contain a contiguous set of dates. If the current context is not a contiguous set of dates, then SAMEPERIODLASTYEAR will return an error.

The last four functions in this category are a bit more complex, and also a bit more powerful. These functions are used to shift from the set of dates that are in the current context to a new set of dates.

  • DATEADD (Date_Column, Number_of_Intervals, Interval)

  • DATESBETWEEN (Date_Column, Start_Date, End_Date)

  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)

DATESBETWEEN calculates the set of dates between the specified start date and end date. The remaining three functions shift some number of time intervals from the current context. The interval can be day, month, quarter or year. These functions make it easy to shift the time interval for a calculation by any of the following:

  • Go back two years

  • Go back one month

  • Go forward three quarters

  • Go back 14 days

  • Go forward 28 days

In each case, you need only specify which interval, and how many of those intervals to shift. A positive interval will move forward in time, while a negative interval will move back in time. The interval itself is specified by a keyword of DAY, MONTH, QUARTER, or YEAR. These keywords are not strings, so they should not be in quotation marks.

Functions that evaluate expressions over a time period

This category of functions evaluate an expression over a specified time period. You can accomplish the same thing using CALCULATE and other time-intelligence functions. For example,

= TOTALMTD (Expression, Date_Column [, SetFilter])

is precisely the same as:

= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])

However, it's easier to use these time-intelligence functions when they are a good fit for the problem that needs to be solved:

  • TOTALMTD (Expression, Date_Column [, SetFilter])

  • TOTALQTD (Expression, Date_Column [, SetFilter])

  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) *

Also in this category are a group of functions that calculate opening and closing balances. There are certain concepts you should understand with these particular functions. First, as you might think obvious, 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 functions always return the value of an expression evaluated for a specific point in time. The point in time we care about is always the last possible date value in a calendar period. 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. The current period is always determined by the last date in the current date context.

Additional resources

Articles: Understand and create date tables in Power Pivot in Excel

Reference: DAX Function Reference on Office.com

Samples: Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel

at

No comments:

Post a Comment