DAX Time Intelligence

This is an introductory lecture on DAX time intelligence function and their dependence on dates and a date (calendar) table.

In this lecture we show how to build a fully functional daily calendar table which can be used by all DAX time intelligence functions.

In this lecture we discuss the amazing calendar created by the SQLBI folks, which includes Alberto Ferrari and Marco Russo. The DAX code that generates this calendar is over 1,500 lines!

In this lecture we show how to build a monthly calendar table based on the weekly calendar table covered in the prior lecture

In this lecture we talk about, and demonstrate, the first batch of time intelligence functions including

• FIRSTDATE

• LASTDATE

• STARTOFMONTH

• ENDOFMONTH

• STARTOFQUARTER

• ENDOFQUARTER

• STARTOFYEAR

• ENDOFYEAR

• FIRSTNONBLANK

• LASTNONBLANK

In this lecture we talk about, and demonstrate, the second batch of time intelligence functions including

• PREVIOUSDAY

• PREVIOUSMONTH

• PREVIOUSQUARTER

• PREVIOUSYEAR

• NEXTDAY

• NEXTMONTH

• NEXTQUARTER

• NEXTYEAR

In this lecture we talk about, and demonstrate, the third batch of time intelligence functions including:

• DATESMTD

• DATESQTD

• DATESYTD

• TOTALMTD

• TOTALQTD

• TOTALYTD

In this lecture we talk about, and demonstrate, the fourth batch of time intelligence functions including:

• OPENINGBALANCEMONTH

• OPENINGBALANCEQUARTER

• OPENINGBALANCEYEAR

• CLOSINGBALANCEMONTH

• CLOSINGBALANCEQUARTER

• CLOSINGBALANCEYEAR

These two functions are very similar but have a bit different usage. We talk about the differences and scenarios for using each

The DATEADD function is probably one of the most frequently used functions when performing any type of time intelligence. We cover it in this lecture, along with two related functions, SAMEPERIODLASTYEAR and PARALLELPERIOD.

We continue our discussion of DATEADD, SAMEPERIODLASTYEAR and PARALLELPERIOD

We continue our discussion of DATEADD, SAMEPERIODLASTYEAR and PARALLELPERIOD

Part 1 of working with the weekly calendar

We continue working with the weekly calendar table by creating some measures that would be quite easy to do using the DAX time intelligence functions, but require a bit more work when working with weekly calendars.

In this lecture we talk about the data model used in this section

In this lecture we demonstrate a number of common business reporting scenarios using the standard daily calendar and built-in DAX time intelligence functions.

This lecture covers creating time intelligence measures to satisfy a business request to display percent of last period totals. The PARALLELPERIOD function is used in a couple measures to accomplish this.

Eventually you will have to generate some sort of rolling average when creating time intelligence based reports. This lecture shows some options for creating a rolling average.

We cover a number of monthly calendar business scenarios and how to create DAX measures that work with monthly based calendars.

We cover a number of weekly calendar business scenarios and how to create DAX measures that work with weekly based calendars.

Test your knowledge by taking this 20 question quiz