A long time ago, I recorded a video on how to use an R visual and DirectQuery to create a chart with a moving X-axis and I thought it was the bee’s knees! Fast forward to today, I realized that it’s super easy to do the trick with SIMPLE DAX and even on an imported dataset. You’ll definitely want to add this trick to your Power BI toolbox, so take a look at the finished gif:
Imagine you want to show this Power BI report on a monitor in a hallway, this report will continue to cycle through data for everyone walking by. Without further ado, let’s create a line chart with a dynamic X-axis!
Side note: I am using data directly out of a database that I have made public to students of the BI Elite Training courses. If you would like to follow along using the same data, you can enroll for the Power BI Beginner course to gain access to the database. End tangent 😊
The first step is to create a disconnected date table as shown below. I’m simply using the following DAX to create a table of dates based on my OrderDate column:
Date = CALENDAR(MIN('Sales Orders'[OrderDate]),MAX('Sales Orders'[OrderDate]))
It’s important to understand why the Date table must be disconnected. For our visual to show a range of dates, there must not be a relationship between the tables. If there were a relationship, the visual would always be filtered down to a single date instead of showing a range of 30 days, for instance. Test this out on your own if you’re curious!
Next, add a Date slicer using the column from the Date table and add a line chart using the OrderDate column from the Sales table and a metric of your choosing. In this example I am using Total Sales as the metric driving my Y-axis. It’s very important that you used different date fields for the date slicer and X-axis of the line chart.
Now, let’s add a “What-if Parameter” in order to allow the user to select the number of days to show in the date range. In the Modelling tab, click on What-if Parameter and create a parameter named “Day Range” with a range of 7 to 90 (or similar) and an increment of 1. Upon clicking OK, a numeric slicer will be added to the page.
Second to last step, replace the metric you’re using with a new measure with the following code:
Sales Within Range =
VAR VisualDate = MAX('Sales Orders'[OrderDate])
VAR SlicerDate = MIN('Date'[Date])
RETURN
IF(
VisualDate >= SlicerDate && VisualDate < SlicerDate + 'Day Range'[Day Range Value],
SUM('Sales OrderLines'[Total Price]),
BLANK()
)
Believe it or not, that’s the entire DAX to create the animated magic. Line 1 simply creates a variable to understand which date we are filtered down to in our line chart. Line 2 creates a variable of the minimum date from our date slicer. Finally, we return our result with an IF statement that says, in words: “If the date from the line chart is greater than or equal to the minimum slicer date and the line chart date is less than the slicer date PLUS the day range value from our what-if parameter, return the SUM of sales, else return BLANK.” Basically, only return a non-blank value for dates within the date range from the minimum date slicer value to the minimum date slicer value plus the number of days selected in the what-if parameter.
Final step, add the “Play Axis” slicer which is a custom visual from the custom visual marketplace. Simply choose “Import from marketplace” and search for Play Axis. Once loaded onto the page, provide it the Date column from the Date table. Click the play button and we’re done 😊 In my example below, I’ve added one extra measure (7-day rolling average) to add a bit of flair.
Bonus: In order to perfect this trick, make sure you click on the Play Axis slicer and edit the Animation Settings. Turn on the Auto Start and Loop options and set the Time interval to be whatever you want. I find that 500 ms cycles through the data efficiently. Finally, the date that shows on the Play Axis might look a bit too detailed. I recommend creating a text representation of the Date column and sorting accordingly. The picture below shows both steps to achieve a text-like date column sorted by Date. Finally, use this column in the Play Axis slicer.
And with that, we’re all done! I hope you enjoyed this quick trick on how to create a line chart with a moving X-axis and a dynamic range of days. This is sure to liven up your Power BI report and fits perfectly on a report shown on a monitor or anywhere static. If you would like to gain access to the database used in this trick or would like to learn Power BI from me, check out the Power BI Beginner course. There is a ton of great information and the Intermediate course will be coming very soon. Subscribe monthly for the ridiculously low cost of $29/month to gain access to all BI Elite Training courses and cancel anytime.
