Categories

# The Ultimate Time-Intelligence Pattern

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:

1. Determine the current (date) context
2. Create the newly desired (date) context
3. 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…

YTD v1 = TOTALYTD ( SUM ( ‘Sales'[Amt] ), ‘Date'[Date] ) YTD v2 = CALCULATE ( SUM ( ‘Sales'[Amt] ), DATESYTD( ‘Date'[Date] ) )

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…

Sales Amt – YTD = /* Step 1. Detect Current Context */ VAR __current_date = MAX( ‘Date'[Date] ) VAR __current_year = YEAR( __current_date ) /* Step 2. Modify Context */ VAR __dates_ytd = FILTER( ALL( ‘Date’ ), AND( ‘Date'[Year] = __current_year, ‘Date'[Date] <= __current_date ) ) /* Step 3. Compute metric in modified context */ VAR __returnValue = CALCULATE( SUM( 'Sales'[Amt] ), __dates_ytd ) RETURN __returnValue

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 */

VAR __current_date = MAX( ‘Date'[Date] ) VAR __current_year = YEAR( __current_date )

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 */

VAR __dates_ytd = FILTER( ALL( ‘Date’ ), AND( ‘Date'[Year] = __current_year, ‘Date'[Date] <= __current_date ) )

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…

1. remove all filters from the Date table (line 3)
2. 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 */

VAR __returnValue = CALCULATE( SUM( ‘Sales'[Amt] ), __dates_ytd )

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.

Sales Amt – Prior YTD = /* Step 1. Detect Current Context */ VAR __current_date = MAX( ‘Date'[Date] ) VAR __current_yr = YEAR( __current_date ) /* Step 2. Modify Context */ VAR __dates_ytd = FILTER( ALL( ‘Date’ ), AND( ‘Date'[Year] = __current_year, ‘Date'[Date] <= __current_date ) ) VAR __dates_prior_ytd = CALCULATETABLE( SAMEPERIODLASTYEAR( 'Date'[Date] ), __dates_ytd ) /* Step 3. Compute metric in modified context */ VAR __returnValue = CALCULATE( [Sales Amt], __dates_prior_ytd ) RETURN __returnValue

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.

Sales Amt – Prior YTD = /* Step 1. Detect Current Context */ VAR __current_date = MAX( ‘Date'[Date] ) VAR __current_yr = YEAR( __current_date ) VAR __current_doy = MAX( ‘Date'[DayNumberOfYear] ) /* Step 2. Modify Context */ VAR __dates_prior_ytd = FILTER( ALL( ‘Date’ ), AND( ‘Date'[Year] = (__current_year – 1), ‘Date'[DayNumberOfYear] <= __current_doy ) ) /* Step 3. Compute metric in modified context */ VAR __returnValue = CALCULATE( [Sales Amt], __dates_prior_ytd ) RETURN __returnValue

And while you could easily have written, the following instead…

Sales Amt – Prior YTD = CALCULATE( SUM( Sales[Amt] ), SAMEPERIODLASTYEAR( DATESYTD( ‘Date'[Date] ) ) )

…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…

Sales Amt – SPLY = /* Step 1. Detect Current Context */ /* Step 2. Modify Context */ /* Step 3. Compute metric in modified context */ RETURN __returnValue

Hint: having a good date table really helps with this one 😉

## Wrapping Up

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.

## 3 replies on “The Ultimate Time-Intelligence Pattern” Mustafa Hussain Mohsinsays:

Mr. Bill your blog is really informative, but could you please explain how to make the formula for SALES LY according to your pattern. I am confused a bit.

Like Billsays:

Hi Mustafa,

Thanks for reading and commenting. Assuming for [Sales LY] you mean the quite literal “entire Sales LY” it could look something like this…

``` /* Step 1. Detect Current Context */ VAR __current_yr = MAX( 'Date'[Year] )```

``` /* Step 2. Modify Context */ VAR __dates_ly = FILTER( ALL( 'Date' ), 'Date'[Year] = __current_year ) /* Step 3. Compute metric in modified context */ VAR __returnValue = CALCULATE( [Sales Amt], __dates_ly ) ```

``` RETURN __returnValue ```

…the above returns the total sales for the prior year, regardless of which date column is being filtered in the current context (which may not be what you want). For example, if this measure is placed in a card-visual, and ‘Date'[Date] = March 18, 2020 is applied in a slicer or filter pane, the card will show the total sales for 2019. The same value is returned if the ‘Date'[Year Month Number] = 202001 is applied in a slicer or filter pane.

Like Abdul Rafaysays:

Really impressed on how you comment before you identify the current context and modify the context before coda.

I will also start using this as it makes the code really readable and easy to debug.

Thanks for this blog. Loved it!

Like