Calculate the rolling average for 12 months in #DAX and a nice IF optimization

Alberto published the Rolling 12 Months Average in DAX article on SQLBI a few days ago, which includes interesting consideration about how to avoid the pitfall of touching the boundaries of the Date table, which could result in a calculation error.

More interesting for the geek of us is the optimization of the measure to avoid the IF statement. As you may already know if you watched some of our last events or course, using IF statement in a measure might affect performance, especially (but not only) when you query a Tabular or Power Pivot model from MDX (i.e. from a PivotTable in Excel). In this article, instead of using:

Avg12M := IF ( [Sales] <> 0, <expression> )

the formula is

Avg12M := DIVIDE ( [Sales], [Sales] ) * <expression> )

as you can see, the DIVIDE has the only purpose to return 1 if the value to check is other than 0, and 0 if it is 0. The query plan generated by this expression is much faster than the IF one, and this technique can be used in many other similar scenarios.