Prior Month Calculation with DATEADD

1. From Specific to Flexible: Introducing DATEADD

So far, we've used SAMEPERIODLASTYEAR, a function designed for one specific job. DATEADD is different; think of it as a universal time-shifting tool. It allows you to move any set of dates forwards or backwards by any interval you define (day, month, quarter, or year). This flexibility makes it one of the most important time intelligence functions in DAX.

The syntax for DATEADD is straightforward:

DATEADD(<dates>, <number_of_intervals>, <interval>)

Let's break down the three arguments:

1. <dates>: This is the column of dates from your dedicated calendar table. For us, this will be 'Calendar'[Date].
2. <number_of_intervals>: The number of steps you want to shift. A negative number moves you back in time, and a positive number moves you forward. To get the "prior month," we will use -1.
3. <interval>: The unit of time for the shift. The options are DAY, MONTH, QUARTER, and YEAR. We will, of course, use MONTH.

This article provides a concise explanation of how to use DATEADD to calculate a prior month value.

2. Practical Application: Creating the 'Revenue Prior Month' Measure

Now, let's put theory into practice. We will create a new measure that calculates the total revenue for the month immediately preceding the one in our current filter context.

1. In Power BI, navigate to your measures table.
2. Click New measure.
3. Name the measure Revenue PM (PM for Prior Month).
4. Enter the following DAX formula. Notice how it combines CALCULATE (which you've mastered) with our new DATEADD function as a filter.

Revenue PM = 
CALCULATE(
    [Total Revenue],
    DATEADD('Calendar'[Date], -1, MONTH)
)

How does this work?
For any given month in a visual (say, February 2008), the DATEADD function generates a table containing all the dates from the previous month (January 2008). CALCULATE then evaluates our [Total Revenue] measure within that new, shifted date context.

Verifying Your Measure

To see your new measure in action, add it to the matrix visual you created in the previous lessons.

1. Make sure your matrix has Year and MonthName on the rows.
2. Drag [Total Revenue] and your new [Revenue PM] into the Values field.
3. You should now see that for each row, the Revenue PM value is the Total Revenue from the row directly above it.

Your visual should look something like this, clearly showing the sales from the previous month alongside the current month's sales.

3. Understanding the Nuances: DATEADD vs. PARALLELPERIOD

As you explore DAX, you'll encounter another function called PARALLELPERIOD which seems to do a similar job. While they often produce the same results, there is a critical difference in how they behave, especially with partial periods.

• PARALLELPERIOD always returns the full period. If you are looking at data from January 15th-31st and ask for the parallel period in the previous month, it will return the entire month of December (1st-31st).
• DATEADD shifts the actual dates from your current selection. In the same scenario (data from Jan 15th-31st), it would return data from December 15th-31st.

DATEADD is generally preferred when you want to compare "apples to apples"—for example, comparing the first two weeks of this month to the first two weeks of last month.

This video provides an excellent comparison of DATEADD, PARALLELPERIOD, and our old friend SAMEPERIODLASTYEAR.

This distinction is important. While it might seem minor, understanding it prevents unexpected results in your reports when users start filtering by specific date ranges. As a quick rule of thumb: SAMEPERIODLASTYEAR is simply a shortcut for DATEADD('Calendar'[Date], -1, YEAR). For most other period comparisons, DATEADD is your most reliable and flexible tool.

Conclusion

Great job! You've added another powerful and flexible time intelligence function to your DAX toolkit. You can now shift date contexts with precision, enabling more nuanced and short-term analysis.

Key Takeaways:

• DATEADD is a versatile function for shifting a date selection forward or backward in time by a specified interval (DAY, MONTH, QUARTER, YEAR).
• To calculate a prior month's value, you wrap DATEADD('Calendar'[Date], -1, MONTH) inside a CALCULATE function.
• DATEADD is generally more flexible than specialized functions like SAMEPERIODLASTYEAR or PREVIOUSMONTH.
• DATEADD differs from PARALLELPERIOD in how it handles partial periods, making it ideal for like-for-like date range comparisons.

Preview of the Next Lesson:
We have successfully created the Revenue PM measure. We've done the hard part. In the next lesson, we will use this new measure to calculate Month-over-Month (MoM) Growth. The logic will be identical to the YoY calculations we've already done, proving how reusable these DAX concepts are.