THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

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.

Published Thursday, July 3, 2014 10:51 AM by Marco Russo (SQLBI)
Filed under: , ,



sled said:

'Am probably stupid, but i don't understand the formula and i don't see where is the Rolling average for 12 months compute ? Could you precise a little more with a specific example or something like that ? Thanks Marco.

A stupid guy :)

July 7, 2014 6:19 AM

Marco Russo (SQLBI) said:

It's in the <expression>, described in the article I was commenting (follow the link in the post) :-)

July 7, 2014 9:32 AM

G said:

i am working on trailing 13 months but the formula which is given not working properly so please give me solution descriptive

December 21, 2017 5:58 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement