Sign in or start a free trial to avail of this feature.
1. Using Date Functions
DAX provides various functions that allow you to work with the date fields in your data model. We’ll see how to identify first and last dates in your data and extract various pieces from a date field.
Extracting parts of a date
- Functions such as YEAR, MONTH, DAY can be used to extract parts of a date field
- WEEKDAY and WEEKNUM allow you to extract information that is not immediately visible
- For dates with a time element, you can also use HOUR, MINUTE and SECOND
- These functions work in the same way as corresponding Excel functions
Other useful date functions
- TODAY returns today’s date
- FIRSTDATE and LASTDATE let you find the earliest or latest dates in a dataset
- F4 Enter formula bar
- Alt H, Y2 – Change data type
- Alt H, PT, T – Create PivotTable (in Power Pivot window)
- Alt B, F – Create measure
- Alt N, V – Create PivotTable (in Excel window)
In the previous course, we introduced DAX, the language used to create formulas and functions in Power Pivot.
In this course, we'll look at common areas of analysis, such as date functions and time intelligence, as well as more advanced topics, such as filters and contexts.
In this first lesson, we'll look at date functions.
The primary use of date functions in DAX is to extract or identify elements of a date field.
Date functions in DAX are very similar to date functions in Excel.
We'll start by opening the same pharma data set that we used in previous Power Pivot courses.
As we can see, our data set includes one Date column, representing the date on which we sold a specific product to a specific customer.
Let's create a calculated column, called Year, that will extract just the Year value from every Date column.
We can do this using a function called YEAR.
The YEAR function takes the Date column as its argument.
As we can see, it produces a column showing the year for every date in the data set.
Similar functions are available for month, day, hour, second, and almost any other aspect of date/time data you can think of.
While extracting the year might not seem particularly useful, there are date functions that extract less obvious information from a date field.
For example, we can get the week number from our date field by using the function WEEKNUM.
We'll create a new column, call it Week Number, and use the WEEKNUM formula on the Date column.
This could be useful if we want to analyze our data on a weekly basis.
Another interesting date function is TODAY.
This will return the current day.
Let's use this to examine the number of days since the last sale for each customer.
We'll create another calculated column and call it Days Since Sale.
Fortunately, DAX allows us to use dates in simple arithmetic, so we'll do it here.
We'll find the days since the sale for each row by subtracting the value in the date field from TODAY.
This returns the number of days from each specific sale. However, the values are formatted as dates.
We want these values formatted as numbers.
To fix this, we'll navigate to the Formatting area on the Home tab, select Data Type and then Decimal Number.
Now each row shows the number of days since the transaction.
Let's use this new column in a PivotTable.
We'll create a PivotTable and put it on the existing blank worksheet.
We'll add Customer ID and Days Since Sale to the table.
We want to see the minimum days since sale, which will tell us the number of days since the most recent sale for each customer.
Therefore, we'll select the arrow by Sum of Days Since Sale, go to Value Field Settings, and select Min.
We can see that most customers made a purchase on the last day of the data set, but a few customers have not made a purchase for some time.
We could use this information as a starting point to investigate why customers stop buying the company's products.
Finally, let's find the date of the most recent sale for each product.
To do this, we'll use a function called LASTDATE.
We'll create a measure, call it Last Sale, and enter the LASTDATE function, applied to the Date column.
We'll then select OK to create the measure.
Let's remove this column from the PivotTable and create a new PivotTable on a new sheet.
We'll add the Product Name field to the table, along with the Last Sale measure.
We can see that the most recent sale for most products was September 2017.
However, Slintara had its last sale in September 2015.
Again, this could be used to start an investigation.
Maybe the product has been discontinued.
Let's stop the lesson here.
As we've seen, date functions are pretty easy to work with and allow you to extract virtually any possible element from a date field.
In the next lesson, we'll learn about date tables, an essential part of time intelligence analysis in DAX.