DAX & THE HOT DATE

Microsoft’s PowerBI platform is pretty smart about dates.  It’s possible to create useful and compelling reporting involving time without too much effort, as long as your interests fit the native functionality.

If you have spent any time authoring reports, you are familiar with some of the time intelligence functions.  We all know the native date slicer, and how to use it to narrow down a wide range of dates to find something interesting.

But what if you have only one thing on your mind?  What if you are looking for one HOT date?

There are a number of third party calendar visualizations available in the
PowerBI Marketplace.  They all have their merits.  Calendar by Tallan is one of these, which allows you to shade the cells for calendar days by any measure and rules you care to define.The output can look like this:

Now this is interesting, isn’t it? Suddenly we’ve got a whole calendar full of dates. Some of which, at first glance, sure look more intriguing than others.

Selecting one of these dates will filter whatever date field is used in the calendar to that single day. The other visuals on the page are either filtered or highlighted to that day depending on the behavior selected in the interaction between the two visuals.

And this might be effective if our interest is to simply show a single day’s metrics, elsewhere on the page. However, what if our intent is more ambitious than that? What if we wanted to pick that one hot date and get

everything? You’ve probably noticed that there is relative date logic as part of the native functionality in PowerBI. You’ve also probably noticed that it is relative to TODAY(), the date of viewing the report. In some cases that’s useful, but it doesn’t help us in relation to the most captivating dates in our data.

It’s also possible to use a TOPN filter by date to show only the most recent XX dates, but when that’s filtered by a single date in the calendar, there will only be 1 date to rank.  TOPN won’t help us, either.

And elsewhere, we can often use the DAX ALL() function to eliminate unwanted filters, but in this case we need that date filtered, we just want to use the filtered date from the calendar as a reference date to base the rest of our relative time intelligence calculations from.

What if you wanted to see a trendline of dates relative to the date that attracted you most?  That’s going to take a little more ingenuity.  But to the vector belong the spoils, as Norton Juster once said…

To accomplish this, we’ll need to store the value selected from the calendar independently, while we retrieve that value as a filter against the entire date range in our fact table.

But, reporting is read-only, right?  How do we store a value?  It’s not like we have a little black book to write numbers in.  Or do we?

DAX is your wingman, here.  And the solution is a book, of sorts:  a separate date table that has NO relationship to any of the other dates in your report.  Thankfully, DAX is up to the task.  It even makes this easy.  You won’t need to manually build a table elsewhere and load it into the report.  A single line of DAX code produces the table you want:

Click the New Table button, and input the following code:

This produces a single-column table that begins on the first date of the data in your fact table and ends on the last date in your fact.  This is useful to ensure that there aren’t dates in this reference table that don’t have useful data in the fact.

And now, filtering this new column to a single date effectively stores our hot date selection for use in other pursuits.

We’ll use this new column in the calculated table as the date field for the calendar visual.  But because this table has no relationship to our fact, simply adding the metric to the values field will not produce useful results.  We’d get the aggregate of that metric for all dates in that case.  Instead, you’ll need DAX to filter the metric to the date for each calendar cell, like so:

Click the New Table button, and input the following code:

That nested SELECTEDVALUE function is incredibly useful, here.  The syntax for the function is:

Discussion of DAX context is outside the scope of this article, but I HIGHLY suggest following the linked article from Marco Russo and Alberto Ferrari at SQLBI.com and reading up on the subject, even if you think you might understand it already. Without overstatement, context is one of the most critical concepts to mastering DAX and PowerBI.

The function returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise it returns alternateResult.  So in this case, when a single date is selected in the calendar visual, we return that date.  Otherwise, we return the last date in the table.

In each cell of the calendar visual, the context of that date column is a single date.  So we return only that single date. We then use that output to filter the date column in the facttable by that date, effectively returning only the sum of metric for each date in the calendar.

We can use this same pattern anywhere we want to present the most recent value for a metric.  In contexts without a date grain, it will display only the most recent date’s data.

If we need to do calculations at this most recent daily grain, we also need to prepare all terms of the calculation similarly.  To find YoY variance, we need to calculate the previous year’s value by wrapping the first calculation in a second with the date offset, like so:

Note that in this case it is important to use a date dimension for the DATEADD() function, because DATEADD requires a contiguous date range in order to function.

Once this is done, we can calculate the % Variance:

These steps get us closer, certainly.  But all these do is let us see which dates look interesting.  This still doesn’t solve for a trendline relative to the selected date.  We can’t use this calculation because it filters the fact table to only the single most recent date selected in the reference table.

In order to produce our trendline, we can take one of two approaches.  We can build a calculated measure that returns a positive when the dates are in the desired range, and use that measure as a filter for the visual.  Taking this approach is simplest, but the additional queries produced can impact performance of the report.

The logic for this calculated filter follows this pattern:

Add this statement to the filters for the visual and set the advanced filter settings to IS and 1.

Where performance is paramount (and when is it not?), we can also build a calculated measure that only returns metric values when the dates are in this desired range.  This gets highly repetitive when there are multiple measures involved, but the performance gains may be worthwhile.  In either case, using such measures limits the values for our visual the same as the filter.

In either case, we’ll be using the date values from our fact (or from our date dimension, if we have one) for the date axis of the visual, which will look something like this:

The DAX pattern for the metric is as follows:

In this case, we are modifying our logic to return all data with a date between the selected date in the reference table and the number of days we have selected as our offset.  We then use the date field in the fact table as the axis for presenting this data.

The approach is similar for the YoY comparison, but wrap that in another CALCULATE() to shift it to one year in the past using DATEADD().  For the DATEADD() we use the date field from our date dimension table because this function needs the entire date range to be contiguous to work without error.

Once we have these functions built, we can flesh out the reporting view to deliver something that looks like this:

And when that hot date is clicked, the whole sheet updates:

See the live sheet embedded: