Power BI Intermediate Level: 50 - Creating Month-Over-Month and other Time-Based Measures

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You can use DAX time intelligence functions to build measures for previous year's sales, previous month's sales, and so forth. You can then use those measures to build year-over-year, month-over-month, and similar measures. You can also build time aggregates like year-to-date, month-to-date, and more.

Let's now start using DAX time-intelligence functions to build time-based measures. Make sure that you have a custom calendar table with a relationship to fact table which you want to analyze. Here I will teach you two main uses for the time-intelligence functions:

1. Build measures for comparing different time periods, either absolutely or as a percentage.
2. Build measures for time aggregates like year-to-date, month-to-date, etc.

So let's get into those two main uses.

Creating Measures for Week over Week Changes

Let's assume we want to know the week-over-week changes in sales. To show you the numbers directly, let's use a Matrix visual for display.

Using our sample data, we already have a measure for displaying the sum of sales without shifting in time, it is just a simple sum:

48 Sum of Sales = SUM('46_FACT_Transactions'[Sales Amount Before Discount])        

You can ignore he discount, we are not using it here. Next, we want a measure for the previous week's sale. For this let's have a look at the documentation for DAX time intelligence functions. The most simple and useful function for shifting dates is DATEADD.

We can supply DATEADD as an argument to CALCULATE. We need to specify to shift the date in the calendar seven days into the past, therefore -7 days:

50 Sum of Sales Previous Week = 
CALCULATE(
    [48 Sum of Sales],
    DATEADD('48_DIM_Calendar'[Date], -7, DAY)
)        

Let's look at the results in Power BI:

For most weeks, the result is as expected. However you can see some behavior with time intelligence functions that you need to be aware of. It seems peculiar at first but makes sense when you really think about the filtering:

• Calendar weeks may be split. Take calendar week 6 for example: Because we included months in the hierarchy, calendar week 6 is split over January and February. In January we have the two days Jan 30 - 31 and in February we have the five days Feb 1 - 5. In our measure, these get shifted 7 days back. One way to get full calendar weeks would be to remove the month from the hierarchy but we still get a split of calendar weeks over new-year.
• Sometime you have missing data for dates. The January component of week 1 2022 is the single day Jan 1, 2022. Shifting by -7 days yields Dec 25, 2021. But we don't have data for year 2021.
• The subtotals and grand totals often require some special thought. Take the subtotal for January 2022 for example. January 2022 is Jan 1 - 31. If we shift that by -7 days, we get the dates Dec 25, 2021 - Jan 24, 2022. While this is mathematically correct, it is most likely irrelevant and confusing. In many cases, you may not want disable the display subtotals and grand totals.
• Months vary in length, have different numbers of public holidays, etc. You should keep that in mind when performing analysis and not just look at the raw numbers.

So let's improve the Matrix visual. Let's remove the months from the date hierarchy and remove subtotals and totals. Let's also add measure to get week-over-week absolute and percentage changes. We create the measures through simple subtraction and division.

50 Sum of Sales Week-over-Week Absolute Change = 
[48 Sum of Sales] - [50 Sum of Sales Previous Week]        

50 Sum of Sales Week-over-Week % Change = 
DIVIDE(
    [50 Sum of Sales Week-over-Week Absolute Change],
    [50 Sum of Sales Previous Week]
)        

Now that you understand the calculation, let's produce a nice graph with the sales as columns and the percentage change as a line.

Creating Measures for Month-Over-Month Changes

In many cases, you will want to calculate month-over-month or year-over-year changes. We need to consider that months have different numbers of days. We can again use the DATEADD function, specifying a shift of -1 months.

50 Sum of Sales Previous Month = 
CALCULATE(
    [48 Sum of Sales],
    DATEADD('48_DIM_Calendar'[Date], -1, MONTH)
)        

Or alternatively we can use the function PREVIOUSMONTH.

50 Sum of Sales Previous Month option 2 = 
CALCULATE(
    [48 Sum of Sales],
    PREVIOUSMONTH('48_DIM_Calendar'[Date])
)        

Note that there are also PREVIOUSYEAR, PREVIOUSQUARTER, and PREVIOUSDAY functions, but not a function for previous calendar week.

Let's quickly create the absolute and relative month-over-month changes.

50 Sum of Sales Month-over-Month Absolute Change = 
[48 Sum of Sales] - [50 Sum of Sales Previous Month option 1]        

50 Sum of Sales Month-over-Month % Change = 
DIVIDE(
    [50 Sum of Sales Month-over-Month Absolute Change],
    [50 Sum of Sales Previous Month option 1]
)        

You can also use the Quick measure wizard as shown below to create some, but not all time intelligence calculations.

In that case, Power BI creates the measure in a similar way like we did:

50 Sum of Sales MoM% = 
VAR __PREV_MONTH = CALCULATE([48 Sum of Sales], DATEADD('48_DIM_Calendar'[Date], -1, MONTH))
RETURN
	DIVIDE([48 Sum of Sales] - __PREV_MONTH, __PREV_MONTH)        

Time Aggregates like Month-to-Date

If you want want a cumulative time measure over time, you need to think at what date intervals the measure should reset to 0. The usual choices are month-to-date (MTD), quarter-to-date (QTD), and year-to-date (YTD). For example with MTD, for any specific date we would get the sales since the start of the month. Let's quickly create a sales MTD measure using the Quick measure wizard.

As you can see the sales get summed up until and reset at the beginning of each month

Conclusion

Time-based measures in Power BI are very powerful. The main use cases are absolute and relative changes over time intervals like week-over-week and time aggregates like month-to-date (MTD). For some time-based measures you can use the Quick measure wizard to build the measure for you. Keep in mind that you should use a custom calendar table with a relationship to the fact table via the data columns. Also keep in mind that when performing time intelligence, effects like different numbers of days and public holidays in a month can affect your analysis result.

Congratulations on making it through this long but important article!

Next up, I will show you how you can toggle relationships in calculations to become even more flexible in your analysis.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Using Parameter Tables and Toggling Relationships