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 of information 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
Other useful date functions
- TODAY returns today’s date
- FIRSTDATE and LASTDATE let you find the earliest or latest dates in a dataset
In the previous course, we introduced DAX, the language used to create formulas and functions in Power BI. In this course, we'll look at common areas of analysis in Power BI, including date functions and time intelligence. We'll also look at more advanced concepts such as filtering, and evaluation contexts. Understanding these concepts is key to mastering DAX in Power BI. We'll be using the same pharma data set we saw in the previous course, along with an additional workbook. 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. Our goal in this lesson is to create columns and measures using these date functions.
We'll start by looking at our data set in data view. As we can see, our data set includes one date column, representing the date on which we sold a particular product to a customer.
Let's create a calculated column, and extract just the year from the date column. We can do this using a function called YEAR.
This function takes on argument.
In this case, the argument is our date column.
We'll press enter to create the column, and see that is produces a column showing the year for every date in the data set.
Similar functions are available for other date/time segments, such as month, day, hour, and second.
Extracting the year might not seem particularly useful, however, there are functions which allow us to extract less obvious information from the date field.
For example, we can get the week number form our date field by using the function WEEKNUM.
Let's create a new column that accomplishes that task now.
We'll name this column week number, enter the function WEEKNUM, and again, choose the date field as our argument.
We'll then press enter to create the column.
This information could be useful if we want to analyze our data on a weekly basis. As Power BI date hierarchy does not support weekly analysis.
Another interesting function is today.
This function returns the current day.
In this case, we'll use it to examine the number of days since the last sale for each customer. To accomplish this, we'll create a calculated column called days since sale.
To calculate this information, we'll enter the today function, followed by a set of open and closed brackets, and then subtract the date column.
We'll press enter to create the column, and see that it returns the number of days from each particular sale, formatted as a date.
We want to format these entries as a number, so we'll navigate to the modeling tab, and change the data type to whole number.
Let's use this column in a visual.
We'll go to report view, and create a stacked bar chart.
We'll expand this chart so it covers the left half of the canvas, and then drag customer ID to the access well, and days since to the values well.
We want to find the number of days since the most recent sale for each customer, so we'll change the aggregation of the days since sale field from sum to minimum.
Looking at this chart, most customers have not made a purchase for 234 days, as this is when the data set ended.
However, there are several customers who have not made a purchase for a longer time.
A field like this could be useful if a company was creating a marketing campaign targeting lapsed customers.
Finally, let's find the date of the most recent sale for each product.
To do this, we'll create a measure using the function LASTDATE.
As you might expect, this function returns the latest date from a column of dates.
We'll navigate to the modeling tab, select new measure, and call is last sale.
We'll then enter the LASTDATE function, and choose the date column as the argument.
Let's create a table to display the results of this measure.
We'll then add the product names field and the last sale date measure to this table.
We'll navigate to the formatting section of the visualizations pane, and change the text size to 12.
We can see that the most recent sale for most products was in September 2017.
The exception is Slintara, which last had a sale in 2015.
Determining the exact reason for this outlier, will require further investigation by the company.
As we've seen in this lesson, date functions in DAX are easy to work with and allow you to extract virtually any possible element from a date field. In the next lesson, we'll start looking at time intelligence functions, with a focus on date tables.