Using DAX to Build Measures in Power BI
Augment your Power BI data model with custom DAX measures to improve efficiency and provide better metrics for your users. Power BI comes with an extensive range of built-in functions that assist with many common report scenarios. But sometimes, you'll need to create your own measures where the "out of the box" ones don't do exactly what you want or exhibit unintended behavior.
In addition to scenario-specific functions, Power BI has general-purpose functions that you can use in DAX statements to create complex custom measures. This course explores the use of specific and general functions with DAX to address several scenarios.
- Learn how to replace numeric columns with measures that use SUMX and CALCULATE functions
- Learn about Time Intelligence functions and how to make year-on-year and month-on-month reports using time-based functions and custom DAX measures
- Learn how to use Time Intelligence functions and custom DAX to create semi-additive point-in-time measures
This course is intended for anyone who wants to learn how to use DAX to build measures in Power BI.
To get the most out of this course, you should have a basic understanding of Microsoft Power BI.
In Power BI, time intelligence refers to a set of built-in functions to assist with common date based aggregation and slicing scenarios. As you can see, there are quite a few of these functions with names that give a good indication of their purpose, like opening and closing balances for months, quarters, and years. There are functions for totaling those same periods, as well as next and previous period functions. In this demonstration, I want to look at a common scenario where we can utilize some of these functions to produce a typical sales report over time and compare year-on-year and month-on-month growth.
I'll start with a report table, drop my date hierarchy onto it, and remove all of the fields except for year month, which is the date year multiplied by 100 with the month number added to it. Next, I'll add my sales measure, a simple sum of the LineTotal field, so we're starting with sales summarised by month. Next, I'll add a year-to-date sales measure using the TotalYTD function that takes an expression as its first parameter, which will be the sales measure, followed by a date series. When you see the argument dates, as opposed to date the singular, you need to supply a series of dates, as in dates from a date field. We can see that the TotalTYD function cumulatively sums the current period up until the end of the calendar year. Where the dataset starts and the month equals one or January, the sales and sales year-to-date figures are the same. Otherwise, each year-to-date row is the Sum of previous months' sales up until December or month 12. I can do the same with the TotalQTD function even though quarters aren't defined in my FiscalYear dates table as I'm using date, the most granular field, and Power BI takes care of the rest. This organization also categorizes sales by weeks, where week one is the beginning of the financial year that starts on the 1st of July. Looking at week 53 in June 2009, we can see the sales year-to-date is still accumulating, which is not what we want if our fiscal year ends in June. I'll create another column, sales fiscal year-to-date, which will use the same TotalYTD function, but this time I'll add a year end date using double quotes with the date specified as month-year. In the TotalYTD function definition, you can see that there is another optional parameter, filter appearing before year end date. You don't need to add an extra comma or a placeholder parameter as Power BI can tell what you mean from the parameter data type. Now we can see sales fiscal year-to-date resetting to the 1st of July value.
Another common report requirement is comparing values from one period to the corresponding previous period. I'll create a year-on-year measure using the calculate function, specifying the sales measure as my expression and the SamePeriodLastYear function, passing in the date field from FiscalYear. I'll get rid of the week number column to see better what's going on and make the columns the same precision for easy comparison. To see the year-on-year growth is a simple case of subtracting the year-on-year measure from the original sales measure. While the year-on-year growth figures in the first financial year may be technically correct, you probably don't want to see them in most scenarios. This is an easy enough to fix. I'll wrap the expression in an if statement, testing for a blank value. We've got our year-on-year column, but the rows are by months, so ideally, you would also like to see month-on-month data. Using the calculate function specifying sales as our expression, this is easy to do using the generic function ParallelPeriod. ParallelPeriod takes dates as the first parameter, then the interval offset, followed by the type of interval. So with months as our interval, we can use -1 as an offset to look back one month. The month-on-month column now displays the same value as the sales column but from the previous month.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.