Excel DAX - Using Time Intelligence

You have learnt about the DAX powerful feature Time Intelligence in the chapter – Understanding Time Intelligence. In this chapter, you will learn how to use DAX time intelligence functions in various scenarios.

DAX time intelligence functions include −

Functions that help you retrieve dates or date ranges from your data, which are used to calculate values across similar periods.
Functions that work with standard date intervals, to allow you to compare values across months, years, or quarters.
Functions that retrieve the first and last date of a specified period.
Functions that help you work on the opening and closing balances.

Calculating Cumulative Sales

You can use DAX time intelligence functions to create formulas for calculating cumulative sales. The following DAX functions can be used to calculate closing and opening balances −

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the month in the current context.

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the month in the current context.

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the quarter in the current context.

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the quarter, in the current context.

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the expression at the last date of the year in the current context.

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) − Evaluates the expression at the first date of the year in the current context.

You can create the following calculated fields for the product inventory at a specified time by using the following DAX functions −

Month Start Inventory Value: = OPENINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Month End Inventory Value: = CLOSINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter Start Inventory Value: = OPENINGBALANCEQUARTER ( 
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter End Inventory Value: = CLOSINGBALANCEQUARTER ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year Start Inventory Value: = OPENINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year End Inventory Value: = CLOSINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Comparing Values across Different Time Periods

The default time periods supported by DAX are months, quarters, and years.

You can use the following DAX time intelligence functions to compare the sums across different time periods.

PREVIOUSMONTH (<dates>) − Returns a table that contains a column of all the dates from the previous month, based on the first date in the dates column, in the current context.
PREVIOUSQUARTER (<dates>) − Returns a table that contains a column of all the dates from the previous quarter, based on the first date in the dates column, in the current context.
PREVIOUSYEAR (<dates>, <year_end_date>]) − Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context.

You can create the following calculated fields for calculating sum of sales in the West region at the specified time periods for comparison, by using the DAX functions −

Previous Month Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
)

Previous Quarter Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
)

Previous Year Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
)

Comparing Values Across Parallel Time Periods

You can use the DAX time intelligence function PARALLELPERIOD to compare the sums across a period parallel to the specified time period.

PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)

This DAX function returns a table that contains a column of dates representing a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward or backward in time.

You can create the following calculated field for calculating the previous year’s sales in West region −

Previous Year Sales: = CALCULATE ( 
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year)
)

Calculating Running Totals

You can use the following DAX time intelligence functions to calculate running totals or running sums.

TOTALMTD (<expression>,<dates>, [<filter>]) − Evaluates the value of the expression for the month to date in the current context.
TOTALQTD (<expression>,<dates>, <filter>]) − Evaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the year-to-date value of the expression in the current context.

You can create the following calculated fields for calculating running sum of sales in the West region at specified time periods, by using the DAX functions −

Month Running Sum: = TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey])

Quarter Running Sum: = TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Year Running Sum: = TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Calculating a Value over a Custom Date Range

You can use DAX time intelligence functions to retrieve a custom set of dates, which you can use as an input to a DAX function that performs calculations, to create custom aggregates across time periods.

DATESINPERIOD (<dates>, <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.

DATESBETWEEN (<dates>, <start_date>, ) − Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DATEADD (<dates>,<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.

FIRSTDATE (<dates>) − Returns the first date in the current context for the specified column of dates.

LASTDATE (<dates>) − Returns the last date in the current context for the specified column of dates.

You can create the following DAX formulas for calculating the sum of sales in the West region over a specified date range, by using the DAX functions −

DAX Formula to calculate the sales for the 15 days prior to July 17, 2016.

CALCULATE ( 
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day)
)

DAX Formula to create a calculated field that calculates the first quarter 2016 sales.

= CALCULATE (
   SUM (WestSales[SalesAmount]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
)

DAX Formula to create a calculated field that obtains the first date when a sale was made in the West region for the current context.

= FIRSTDATE (WestSales [SaleDateKey])

DAX Formula to create a calculated field that obtains the last date when a sale was made in the West region for the current context.

= LASTDATE (WestSales [SaleDateKey])

DAX Formula to calculate the dates that are one year before the dates in the current context.

= DATEADD (DateTime[DateKey],-1,year)