Data Analysis Expression (DAX)

1. Find the typical YoY growth?

Concepts touched: most frequent value, previous year

We will solving this problem by solving two smaller problems. First, specifying what ''typical'' means and implementing it and secondly by implementing year over year (YoY) growth.

Think about it for a second, then read on, but, if I was in your place, who might be just starting out with DAX, I would do this.

  1. Think how this could be accomplished and take an informed guess - this typically involves guessing the words.
  2. Locate the canon(authoritative collection of text) and search for those words first, then, take a first scan through those pages.
  3. Use your experience and ask yourself, am I facing a lack of knowledge ? If so take a step back and read at a higher level to get the map of the land, again all of this is available in a widely known canon of knowledge like DAX reference.
  4. Find a low friction trial and error environment like an online DAX playground - dax.do

A possible solution

Calculate the metric for the same period but last year:
We could find the dates in the range and by taking the maximum and minimum and subtracting one from the year componnent of all those year we could arrive at the previous year ... Yes we could do that, but nobody does that, as we have a built-in function SAMEPERIODLASTYEAR (see code). It asks for a table which has all the date values from the very start to the end of time, like a calendar.
Most frequent value in a table:
We are choosing to define "typical" to mean the most frequently occuring value. If you search DAX reference for MOSTFREQUENT you will not find any built in function. Which means we have to built it ourselves.
MOSTFREQUENT can be found by counting the number of times each thing appears in a table and choosing the thing with the most count. Which means this subproblem has two subproblems within it. First to count each thing(COUNTOF), secondly to use that count, to find the thing with maximum count(MAXIMUMVALUEISFOR).
Now here is the trick, COUNTOF is implemented by using GROUPBY, CURRENTGROUPand COUNTX. (see code)
  1. Counting Each Thing, we are calling it as 'COUNTOF
    For doing count of values in a column we have COUNT, but that counts the entire table. So if we could partition the table based on the thing that we what to count, and then do the count of each of those paritions, we may be able to accomplish it. This can be done by GROUPBY. (function parameters)
    1. X Function
      This is were we specify what we will do. If we are counting the things. Then use COUNTX, if we are summing up, use SUMX, if we are finding average use AVGX etc.
    2. CURRENTGROUP
      This refers to the partitions that result from grouping the rows based on a column value.
  2. Get Max Valued Thing, we are calling it as MAXIMUMVALUEISFOR
    To find the rows with the top values in a column. We have TOPN . (see code)