Power Pivot Principles: The A to Z of DAX Functions – FIRSTDATE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at FIRSTDATE. 

The FIRSTDATE function

The FIRSTDATE function is one of the time intelligence functions, it returns the first non-blank date.  It has the following syntax:

• dates: this is required which represents a column that contains dates.  This can be any of the following:
  • a reference to a date / time column
  • a table expression that returns a single column of date / time values
  • a Boolean expression that defines a single-column table of date / time values.

It should be further noted:

• the FIRSTDATE and LASTDATE functions will return the same day if the current context contains a single value
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Since the FIRSTDATE function is a time intelligence function, it should be noted that:

• all dates need to be present for the years required.  It is suggested that all the days in this span, from January 1 to December 31, must be included in the Datetable.  The date table must include all dates from commencement to the last day of a fiscal year if the report solely refers to fiscal years
• a column / field with a DateTime or Date data type and unique values is required.  Typically, this column is known as Date.  Although it is common practice, this is not necessary when defining associations with other tables.  However, the ‘mark as Date Table’ feature should relate to the Date column, which must have distinct values
• the Date table must be designated as a date table in the model in case the relationship between the Date table and any other table is not dependent on the Date.

Let’s consider the following example.  We have the following Sales table with 60,398 rows of data:

We will create a PivotTable that grabs the first OrderDate of every ProductKey.  Therefore, we will create the following measure:

Then, we insert a PivotTable from the data model and drag the newly created measure and ProductKey into the corresponding PivotTable Fields:

This will return the following PivotTable:

Since the database data started on the 18^th July 2013, the first date for all ProductKey values here will be displayed in column B as shown.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.