The Most Powerful Force In The Universe, In DAX

Matt Allington talks about compound interest and shows how to calculate it in DAX:

Now back to the point of this article.  Compounding growth is very easy to do in Excel because you can write individual cell formulas (to do what ever you want), and each new formula can reference the answer from the previous formula as the starting point for the new formula. There is no such ability in the DAX language.  To solve such problems in DAX, you have to change the way you think and start to think about how to write a single formula that will work over an entire TABLE of data (or columns or multiple tables) – no cell by cell individual formulas are possible.

Below I will step you through the process of finding a solution to this problem.  As I often mention in my articles, it is the process that I believe is most important.  I seldom know how to answer a complex DAX problem when I start out (that’s the very definition of “complex”), and instead I follow a process to help me solve the problem.  Take a careful read below.  If you apply the same process when you write your formulas, you will be well on your way to becoming a DAX Superhero.

It’s an interesting problem when your growth rate is not always the same, but Matt has you covered.

Related Posts

Blank Rows and DAX

Alberto Ferrari explains how different DAX functions treat blank rows differently: DAX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The difference between the two is subtle. To understand it better, we first need to introduce the concept of the blank row. The blank row is a special row added […]

Read More

Power Query and the Benefits of Immutability

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes: Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30