Select Page

Since I receive questions about this topic from my clients I decided to dedicate a blog to it. What is the difference between DATEADD and PREVIOUSMONTH? Isn't the result the same? The answer is that it sometimes is, but certainly not always.

In this scenario, the result is the same at the month level
DATEADD =
CALCULATE (
[Sales],
DATEADD ( dim_Calender[Date] , -1 , MONTH )
)

PREVIOUSMONTH =
CALCULATE (
[Sales],
PREVIOUSMONTH ( dim_Calender[Date] )
)

This is simply going to give the Sales of the month before the one in the initial filter context. The only difference here is the result at the year and total level. DATEADD will give the sum of the results at the month level, while PREVIOUSMONTH gives the first month of the year at the year level and no result at the total level. In order to provide for a result at the year and total level which matches the one given by DATEADD an iterator is required going by all years and all months and then summing the results which can by done by SUMX.

PREVIOUSMONTH =
SUMX (
VALUES ( dim_Calender[YearMonth] ),
CALCULATE (
[Sales],
PREVIOUSMONTH ( dim_Calender[Date] )
)
)

But the real difference can be seen when we drill down to the individual day level.

PREVIOUSMONTH, similar to PARALLELPERIOD, returns the result for the entire previous month, even though the individual day is entered into the filter context. Neither is "wrong" or "right", it all depends upon the information requirement of the user of the dashboard.

Now let's look at how we can obtain the sales of 2 months ago. This is the adjusted code for the two formulas.

DATEADD =
CALCULATE (
[Sales],
DATEADD ( dim_Calender[Date] , -2 , MONTH )
)

PREVIOUSMONTH =
CALCULATE (
[Sales],
PREVIOUSMONTH ( PREVIOUSMONTH ( dim_Calender[Date] ) )
)

The same behavior can be observed. However, this time, PREVIOUSMONTH should be replaced by PARALLELPERIOD. It is easier to write and read and has faster performance. Here is the code of the latter:

PARALLELPERIOD =
CALCULATE
[Sales],
PARALLELPERIOD ( dim_Calender[Date], -2, MONTH )
)

By the way, here is the code for the Sales table I've used for this example:

Sales =
ADDCOLUMNS (
CALENDER (
DATE ( 2017, 01, 01, ),
DATE ( 2020, 07, 10, )
),
"Value",
DATEDIFF ( DATE ( 2020, 07, 10 ), [Date], DAY )
)