Showing Results Before Or After A Selected Date Using DAX

Showing Results Before Or After A Selected Date Using DAX

13 comments

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.

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.

selected date

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.

selected date

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.

selected date

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.

selected date

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.

selected date

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:

selected date

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.

selected date

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.

***** Related Links*****
Show Results Up To Current Date Or A Specific Date In Power BI
How To Work With Multiple Dates In Power BI
Time Comparison For Non Standard Date Tables In Power BI

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

Enterprise DNA Power BI On-Demand

13 comments on “Showing Results Before Or After A Selected Date Using DAX”

  1. Hi SAM MCKAY,
    Thanks for explanation. I am expecting this logic to be implemented in Bar chart. But instead of Date field, can we achieve the same logic with some other column value(category, color, or something else)?

    1. Hi Vignesh – Thanks for your interest in Enterprise DNA Blogs.

      Yes, you can use Category also considering you have use case for it. Also DAX formula will change as Today() is related to date format.

      Please feel free to ask any other query related to this Blog Post.

  2. Hi Sam,

    This is excellent and has helped a lot. Thanks for sharing.

    QUESTION: How can I also show a point in time piece of data from the fact table whilst showing the time series chart also on the same page? i.e. I want to select Dec 20 from a slicer and show the 5 periods of valuation data before on a line chart (using code above), and also show another table with just the Dec 20 values (being category values, i.e. name of external auditor, which can change between periods).

    Thanks
    Scott

    1. Hi Scott Drummond – Thanks for your interest in Enterprise DNA Blogs.

      Looking at the requirements, it will require creating two separate measures. one for showing 5 periods of evaluation and other for showing data for selected dates only as given below.

      Sales for selected date =
      VAR SelectedDate = CALCULATE( MAX( ‘Selection Date'[Date] ), ALL( Dates ) )

      RETURN
      CALCULATE([Total Sales],Dates[Date] = SelectedDate)

      You may need to modify the measures based on your exact requirement.
      For further help on this , please raise a ticket at EDNA Forum https://forum.enterprisedna.co/

      Please feel free to ask any other query related to this Blog Post.

  3. Hi Sam.
    I have a somewhat similar use case, would like to use this method, but something is not clicking for me.
    So, I have multiple files I import into PowerBI.
    They all have the import date in a column, so basically one date/import in the import column.
    My goal is to create 3 bar charts. First 2 are for the values and both need to have a slicer to be able to select the needed data set by date, and the third chart should show the Delta.
    Imagine having some evolving values in an excels. When you import the excels, you set the import date as the Date and you want to compare how the values evolved between any 2 imports.
    I looked at multiple options, this seems to be the closest to my particular use case, but I can’t make it work. First, I don’t have a separate calendar, I am using the dates from the data source.
    From what I can tell in the tutorial, the Calendar is generated on the Power Query side, not Dax, otherwise you couldn’t use it a reference for the new date table. When I try to do the same, the relationship fails. I tried creating a Calendar in Power Query, got the same result unfortunately.
    So at the end of the day, I need to be able to capture the “between” dates that are decided by the 2 slicers so I can to a Measure between the two for the third chart. Any help is appreciated.
    Thanks you.

    1. Hi Marius,

      Thanks for your interest in Enterprise DNA Blogs.

      For creating a date table in Power BI, can refer to below Blog post
      https://blog.enterprisedna.co/how-to-create-a-detailed-date-table-in-power-bi-fast/

      For your other requirement, as explained in Blog post need to get the values from Slicer using DAX like max, All as shown in example.

      If further help is needed, please raise a request at EDNA forum forum https://forum.enterprisedna.co/ for further help.

  4. Sam, thank you for this example. One thing I do not understand is why is there a one to many relationship between the dates and selection date tables? They both have the same number of rows and distinct date values.

    1. Hi Dean, thanks for your interest in Enterprise DNA Blogs. Power Bi might have taken 1*Many relationship by default. It can be changed to One to One. Primary thing to consider is the Filter Direction.

  5. Hi Sam,

    I have stumbled upon this blog post that is helping with my use case however I’m stuck on one part. In your tutorial, you have changed the visual from a Table to a Column Chart. I want to leave it in the Table visual however the Total value is missing in your “Sales from Selected Date” Measure. How do you show the total sum of that measure?

    1. Hi Andy,

      Thanks for your interest in Enterprise DNA Blogs.

      In case you are looking to get Total Value i.e. Sum total for all the 3 dates ,then can modify the formula. In Return section instead of Total Sales, give formula like

      Calculate(Total Sales, filter(all(Dates), Dates[Date] < Selected Date - [Day Number] && Dates[Date] < Selected Date)) IF just needs the Sales value, then simply drag Total Sales measure in the Table visual. Hope this is of help. Cheers!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.