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.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:23)
The goal of this lesson is to learn how to use date functions in DAX.
Extracting Elements from a Date (00:29)
DAX includes a variety of functions that can be used to identify a single element from a date field. These functions all take a single argument, which should be a reference to a column of date data, and they return the corresponding element of the date. For example, the YEAR function returns the year component of a column of dates. Similar functions exist for MONTH, DAY, and so on. There are also functions for date components that may not be obvious from looking at the date, such as the WEEKNUM function which returns the week number from a date.
In our case, we use these functions to create new columns containing specific information about the date components. In practice, you would be most likely to use these date functions to build larger or more complex functions.
Using the TODAY Function (02:10)
The TODAY function returns the current date. There are many situations where this can be useful. For example, we create a column in our dataset that subtracts the date value for each transaction from today. This column tells us the number of days since the transaction. Note that when we perform calculations on dates the result is formatted as a date. We can change the type of the result to a number to make the result more logical.
After calculating the number of days since each transaction, we can use the field in a Pivot Table. The minimum value of this field tells us the number of days since the most recent transaction. For example, we can find the number of days since the most recent transaction for each customer. If some customers have not made a purchase in a long time, we can investigate why this might be the case.
Finding the Most Recent Date (04:12)
We can find the most recent date in a dataset using the function LASTDATE. This takes a single date column as its argument and returns the latest date from that column. In our dataset, we create a measure using this function, then add it to a Pivot Table to identify the most recent sale date for each product in our data set. As before, we can investigate if we find that some products have not recorded a sale for a considerable length of time.
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.