1. Dates and Quick Measures
Similar to previous chapters, we’ll be working with the commodity dataset. First, we’ll create our a calculated date table, learn how to apply functions, and understand how to create our first quick measure.
We’ll start by creating a new page called Dates.
In previous lessons we covered the importance of a well-formatted date table - so let’s create it.
Let’s navigate to the modeling tab and select new table. This will open up a new formula bar - we’ll start by naming our table Dates.
Next, we’ll start by using the CALENDARAUTO() which takes an argument for the fiscal year end month.
We’ll be using 12, meaning DAX will return a table from January 2000 to December 2022.
We can now commit this table to our model. Navigating to our model view, we see our new Dates table containing 1 column: date.
Currently, there is no relationship defined between our Commodities table and our Dates table. Let’s create a relationship between the two tables by dragging Date from the Dates table over the date in the commodity table.
Let’s move back to the report view, we can see our Dates table in the field pane.
Awesome! Now that we have a Dates table, we can utilize the built-in date functions in DAX to add additional layers of information to our table.
Let’s create a new calculated column in our dates table called Year.
Using the YEAR function from DAX returns the year from the date argument. In this case, we will be using the Date column from the Dates table. Now we can commit this new column by clicking the checkmark.
As you can see, this new column has been added to our Dates table in the field pane.
Let’s dive into the topic of quick measures! Quick Measures are a feature in DAX that allow you to create complex DAX functions using a drag and drop interface.
We can create a new quick measure from the home pane. Doing this allows you to see the range of calculations that you can use quick measures for, including aggregate, filter, and time-intelligence functions.
Since our primary focus is on working with dates, we’ll be using the rolling average function that will return the average of a base value over a certain period of time.
For our quick measure, we will look at the average close price over a 3-year period. Quick reminder, our dataset has over 20 years of data - so 3 years periods will help us assess the long-term trends.
We’ll be using Close from our commodities table which is the close price of the commodity on that given day. We’ll then adjust the aggregation to Average. Next, we’ll use our newly created Date column from Dates table for the Date. Finally, we’ll adjust the period to Years and leave the periods before as 1 and periods after as 3.
Great! Now if we select our newly created measure,we can see it’s a complex piece of DAX code, this has been used to create this rolling average function.
Let’s visualize the difference between the average Close and the rolling average Close.
We’ll create a line and stacked column chart to compare. Date into our shared axis field. In our column values, we’ll bring in Close from the commodities table and change the aggregation from sum to average using the dropdown. Finally, we’ll drag our newly created quick measure, rolling average as the line value.
As you can see from the graph above, the rolling average is a smooth line. This is because rolling averages are useful for long-term trends and are not prone to occasional fluctuations like we see in 2011 and 2012. Our yearly trends are much more volatile vs our rolling average which shows the general trend moving upwards with a slight dip between 2012-2015.
Now it’s your turn. Let’s practice!
2. Let's practice!