Showing Results Before Or After A Selected Date Using DAX

As we work through this tutorial, I will show you some truly powerful analytics that you can complete inside of Power BI. What we are attempting to analyze here is the information around a selected date. You may watch the full video of this tutorial at the bottom of this blog.

We are going to look at a specific time frame , and then look at a range of days inside the context of the selected date. It could be three days before that date or ten days after that date. It all depends on your selections within the reports. Basically, when you select any time frame in my dynamic filter, you can also filter the results.

Additionally, I’ll show you how to make dynamic visualizations that show results around your date selection.

It is amazing how flexible you can make your solutions in Power BI if you get the set up right.

When I say “set up”, I mean your data model first and foremost, followed closely by adding the right DAX function techniques into your reports.

The flexibility we have around what you can dynamically show within your report is quite phenomenal.

I’m going to work through how you need to set this up in your model as it’s not as simple as just using your common date table. Then, I will show you the DAX formula and logic that you need. This isn’t too difficult, but it is slightly unique.

Table of Contents

Show Results Before Or After A Selected Date

Here, I have mocked up a quick dashboard that shows results before or after a selected date. What I want to do is actually select one of any dates and look back over a certain period of days prior to that selection.

In this sample report, there are various contexts that I can change. For example, I can quickly change the dates around and review the days leading up to that.

Furthermore, I’ve added a “what-if parameter” here for the Number of Days where I can filter in and out. I can change it to a few days or I can take it up to 20 days.

You’ll be really amazed when you understand how this actually works. The hardest thing to solve here is how to show all the days selected in both the date selectors all at once without having an additional filter.

What happens is that you generally select a date that’s going to filter everything on the report page. But as you can see, it does not actually impact any particular visualizations.

The first thing that you should consider is the date column. You might want to use it in the slicer. What you actually want to do is create another table that only has the date column.

Let’s jump back here and see what a generic date table looks like.

Now, I want to create another table because I didn’t want the selection to have a direct effect on the visualization. I just want to extract the date selection and put it in the formula. So, I created a brand new table that looks like this and it just contains the date that I use in the slicer.

Take note that I don’t want to create this column or table by using the New Table function. Instead, I’ll need to jump into the Query Editor.

I have referenced the dates table and removed every other column. This will eventually hard code the table instead of being derived from a formula.

This is called circular dependencies. It means that you can build a relationship or a one-to-many relationship between the date table and the new one. The important thing here is to be able to filter this new table so the slicer will still filter.

Model Relationship Behind The Dates

This is how the model needs to be set up. I created an additional table called Selection Date. Just to make sure, it needs to have a relationship between the Dates table.

The reason why there is a relationship between those tables is because I want the date selector to be dynamic. It needs to expand or contract based on the range of dates that I select in the date slicer.

This is very important because it’s going to be hard to have all the dates listed in the date selector. If the mentioned tables don’t have relationships, you’ll surely find it hard to scroll down all the list of dates in the date selector.

Additionally, you might find blank data in the visualization when some dates in the date selector are outside the context of the date slicer. Thus, the date selector just needs to show the specific dates according to your selected dates in the slicer.

Working Out The Sales From Selected Dates

There are still various ways that you can apply the technique that I mentioned, not just on dates. For example, you can apply this to any lookup table. Once you understand the technique, you can utilize content layer context on top of each other to create different visualizations. This is a great additional knowledge when it comes to analytical perspective and visual perspective.

Now, let’s work out a formula for the visualization of the Sales from Selected Dates by Date table. I’m going to turn it into tables first so we can see what’s really happening.

As you can see, I’ve got two calculations. I have the Total Sales column that shows every single sale per day. Then, the Sales From Selected Date column shows

For example, the selected date is August 26, 2016 and I want to look back three days. Therefore, I just want to show August 26, August 25, and August 24 in the visualization.

I can also change the number of days here, for example, I’ll extend it to 4 days. As a result, the amount of calculations will also be changed.

Here’s the formula for doing that:

I needed to work out the selected dates, no matter what context there was. I also needed to hard code that day in a variable. Lastly, I wrapped it up with ALL(Dates) because that is the date column coming from the date table. So, for every single day here, it places a filter on the Selection Date table.

Since I just want to extract the selected date, I needed to wrap that actual result inside the CALCULATE function with all the dates so that the context is removed.

Then, I had to run some logic. First, it checks if the current date is greater than the difference of the selected date and day number. Second, it checks if the current date is less than or equal to the selected date. Then, it will return the value of Total Sales or else it should be blank.

If I bring it into a visualization, that’s how it’s basically calculated. So you can either change the value in the Day Number parameter or select any particular day in the date selector.

The title of this tutorial might be Show results before OR after a selected date, but you can also apply a real-world application here. Look at the Total Sales by Date table. For instance, you can see this massive sales spike and it could be because of a sales period or some marketing event.

You could view the flow or effect for a certain number of days of that particular event. You can see the entire results on one chart. For example, you want to look at the sales for April 20, 2016. Basically, you need to select 20/04/2016 from the date selector. After a change of formulas, you can then see four days instead of backward dates.

Conclusion

I think you will be quite amazed at how well this can be highlighted inside of a Power BI report.

Duplicating columns and applying different contexts on your report pages are powerful techniques. If you can bring all these together, from the model, DAX formulas, or the selections, you can actually create pretty cool visualizations.

If you want to check out more time intelligence techniques to use with Power BI, I recommend having a look at our course module at Enterprise DNA Online, which specifically caters to a range of analysis tailored to time-related insights.

Enjoy working through this detailed video.

Sam

[youtube https://www.youtube.com/watch?v=MooZofz5GOI&w=784&h=441]