Power BI DAX: DATEADD vs. PREVIOUSMONTH

Home Power BI DAX Power BI DAX: DATEADD vs. PREVIOUSMONTH

Since I get questions from my clients about this topic, 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 sometimes they are, but certainly not always.

In this scenario, the result at the monthly level is the same

Here it is:

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

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

This simply returns the Sales of the month before that in the initial filter context. The only difference here is the result at the year and total level. DATEADD gives 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. To obtain a result at the year and total level that matches the result obtained by DATEADD, an iterator is needed that runs down all years and all months and then sums the results, which can be done with SUMX.

Here it is:

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

But the real difference can be seen when we look at the individual day level.

PREVIOUSMONTH, similar to PARALLELPERIOD, gives the result for the entire previous month, even though the individual day was entered in the filter context. Neither is "wrong" or "right", it all depends on the information needs of the dashboard user.

Now let's see how to get the sales from 2 months ago. This is the custom code for the two formulas.

Here it is:

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

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

Here it is:

The same behavior can be observed. This time, however, PREVIOUSMONTH should be replaced with PARALLELPERIOD. This is easier to write and read and has faster performance. Here is the code for the latter.

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

By the way, here is the code for the table Sales that I used for this example.

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

Categories

Recent Posts