Much like the pieces on a chess board – to the novice, they represent individual units with different capabilities. Over time (or so I’m told) as experience is gained through tournaments and deliberate practice, the view of the novice expands to the point where the student stops seeing “individual pieces” and starts seeing “setups & patterns”.
This same phenomenon – the transition from seeing “individual functions”, to “setups & patterns” – is also true with DAX and I believe the metamorphosis for most DAX students begins with time-intelligence calculations.
In this blog post, I’m going to cover the what I’m now referring to as the Ultimate Time Intelligence Pattern and what has been responsible for a good number of these “ah-ha!” moments I’ve had the pleasure of witnessing with clients and attendees over the last few years.
But first we need lay a foundation…
Time-Intelligence refers to the aggregation and comparison of measures across time. To drive home the nuance of that statement, let’s use an example.
In the table below, what value would you expect to see in the yellow-highlighted cell at the bottom of the Year-to-Date (YTD) column?
If you said $415.00, then give yourself a pat on the back.
The value in the YTD calculation represents the sum of the Actual column for all the days since the beginning of the year up to and including the current date on that particular row.
So for the first row, which is the first day of the year, the YTD, is the same as the actual Sales Amount for that date… $115
The next row, is the sum of both Jan 1 and Jan 2… $115 + $75 = $190
The third row is the sum of Jan 1, Jan 2, and Jan 3… $115 + $75 + $125 = $315
And finally the 4th row… $115 + $75 + $125 + 100 = $415
Translating that over to pseudo-DAX, the YTD amount for Jan 4 (the last row in the YTD column) is computed as follows…
First we determine the current (date) context (Jan 4)
Next, we expand the current (date) context to represent the period of Year-to-Date (Jan 1 to Jan 4)
Finally, we sum our measure (Sales Amount) across our modified date context to arrive at our final result…
That’s it – 3 basic steps:
- Determine the current (date) context
- Create the newly desired (date) context
- Compute the measure in the newly created (or modified) context
Got it? Great!
Ultimate Time-Intelligence Pattern
Now that we understand in plain English how to compute YTD, let’s change languages …like how my grandparents would switch from English over to Greek when their discussion went from conversational to “passionate”.
As is often the case in the crazy world of DAX, the business logic (YTD) can be expressed a number of different ways. Below are perhaps the most common…
Despite the simplicity of these 2 versions of the YTD logic, I frequently suggest new Power BI developers consider the much less trivial formula shown below…
The reason I prefer the above code to the previous 2 (much) simpler versions is because it is a “pattern” – one that (as we shall see) can be easily adjusted to accommodate an incredibly wide range of time-intelligence logic – including scenarios that simply can’t be handled with out-of-the-box DAX functions alone.
Eating the elephant…
To make this a bit more clear, let’s break it down into bite-sized chunks…
/* Step 1. Detect Current Context */
Here we’re simply capturing the current context. For a basic year-to-date calculation, we need to know 2 things about the current context… the current year and the current date.
/* Step 2. Modify Context */
Here we are creating a new date context (some describe this as “modifying” the current context… potato-patato, cucumbers and pickles). We can read the above DAX expression as…
- remove all filters from the Date table (line 3)
- filter the (entire) Date table for rows where the value in the year column is equal to the current year and the value in the date column is less-than-or-equal-to the current date
/* Step 3. Compute metric in modified context */
Now that we have our new context (saved in a variable) we can CALCULATE our measure (sales amount) within that new (modified) date context
Note: Entire books could be written on the power of the CALCULATE function – for now just know that it’s mostly used to manipulate context (later, if you really like this stuff, you can buy a book and really blow your mind)
Back to “Setups & Patterns”
As I mentioned earlier, this is a very versatile pattern. So let’s do another one!
Prior YTD (needed for Year-over-Year comparisons) is nearly identical with the only difference in Step 2 where we modify the context.
Or, and this is where things get fun, we could write the function as follows where we leverage a “helper-column” in our date table called DayNumberOfYear which is simply a number (1 to 365/366) representing the sequential day of the year.
And while you could easily have written, the following instead…
…I’d encourage you to think through how you might handle a more complicated scenarios such as the one shown below where we are attempting to compare the current month (March 2013) with the same month last year (March 2012).
The problem is that our calculation for SamePeriodLastYear (SPLY) is returning the entire month of March 2012 but we’re only 5 days into March 2013. Clearly this is not a fair (apples-to-apples) comparison… 5 days of sales in the current month vs 31 days of sales in the same month last year.
There are a handful of ways to solve this, but I’ll encourage you to consider the pattern discussed in this blog post…
Hint: having a good date table really helps with this one 😉
Time intelligence is about detecting current context and manipulating it to suit your requirements. And while context can be difficult (initially) to wrap your head around I assure you it is a necessary and worthwhile endeavor if you plan on being successful as a Power BI developer.
Still have questions? Reach out below in the comments.
Or if you have pressing issues that require immediate expert level help then contact me here.