Time Series Analysis in Power BI using Timeline Visual


By:   |   Updated: 2019-06-20   |   Comments (4)   |   Related: More > Power BI

Problem

Time is an attribute that can be associated with almost any data element. Almost every analysis use-case directly or indirectly, sooner or later involves time as an attribute or dimension for analysis. Specialized visuals are often employed for analyzing and reporting data over time as the primary attribute. Gantt chart, Stream chart, etc. are examples of such visualizations.  Typical use-cases that need detailed analysis of time-series data are project planning, merchandise tracking, event monitoring, etc. In this tip we will learn the use of a visualization in Power BI to support time series analysis to support these types of use-cases.

Solution

The Power BI visuals gallery provides a timeline visual which can be used for time series analysis.

Typically, the general impression of time series analysis is usage of visuals like a Gantt chart and datasets like project planning tasks and stock movement. But there are some atypical use-cases of time series analysis like comparative analysis of width and progression of data over time. For this we would need a dataset that has such characteristics.  In this tip we are going to perform time series analysis on a dataset. To make the exercise interesting, we will be performing analysis on the dataset containing details of two very popular TV series – Game of Throne’s and Grey’s Anatomy. The dataset looks as shown below.

Data set to use with Power BI

As simple as this dataset looks, there is still some time series analysis that can be done on this dataset. It is assumed that Power BI is installed on the development machine. To start with the time series analysis, we would be using the "as Timeline" chart. You can download this chart from here.

Power BI As Timeline visual from the Microsoft AppSource

Once you have downloaded this chart, open Power BI Desktop. From the visual’s gallery, click on the ellipsis to import a custom visual. Once the dialog box opens to import the visual, select the downloaded visual and add it to the gallery. Upon successful import, the control should look as shown below.

Import the Power BI Timeline visual

Now that we have the visual available for use, let’s add this visual to the gallery and enlarge the visual to occupy the full layout of the screen space.

Add the visual to Power BI from the gallery

To populate the visual, we need the data that we saw earlier. Click on the Get Data menu item from the Home menu, select the appropriate data source, and point to the corresponding table or file. Before importing the data, preview it and make sure that the dataset has all the desired attributes. It’s very important to ensure that the date fields are imported correctly. If the resulting data type is not date, then you would not be able to build the date hierarchy using the date fields, which can cause the visual to not render the visualization.

Seasons data in Power BI

Once the data is imported, the field list would look like the image shown below.

Power BI As-Timeline visual Fields

Add the fields to the chart as shown below. We need both the series on the Y-axis and timeline on the X-axis. So, add the series field in the Entity section and Start Date as well as End Date in the relevant section. Once you add it, the visual would appear as shown below. You can see some bars on the visual, but its not clear enough to perform any time series analysis.

Power BI As-Timeline visual

Navigate to the format section and change the element height from 15 to 100 so that the bars appear clearly on the visual. The first derivation you can make from the visual is that the Grey’s Anatomy seasons are longer than the Game of Throne’s seasons. Another derivation we can make out is that Game of Thrones seasons always started towards the end of the Grey’s Anatomy season. It looks more than a coincidence for 8 consecutive years. Though it’s famously said in statistics that correlation does not mean causation. But it’s an interesting fact that you can derive from the visual.

Power BI As-Timeline visual

The color of the bars is the same, which can make is hard to visualize the difference in categories if the data is voluminous. Add the Series field to Category as well, and that would add individual colors to each series as shown below.

Power BI As-Timeline visual with individual colors for each series

This visual does not clearly show the progression over time for individual seasons of the series. Add seasons to the Category section, and the visual would look as shown below. Now the visual clearly shows the progression of seasons in each series over time. The progression of seasons also shows that Grey’s Anatomy seasons had almost the same number of breaks and same duration for almost every season. If you visually analyze the distance between seasons, you can make out that the distance between last two seasons of Game of Thrones seasons is more than the rest of seasons. The reason is that no season of Game of Thrones was aired in 2018, which becomes evident from the visual.

Another derivation you can make is that the first season of Grey’s Anatomy was very short comparatively to other seasons, and these seasons are longer than Game of Thrones seasons. The black vertical line at the end of the visual shows today’s date. We can switch off this line by navigating to format menu and turning off the today’s bar indicator from behavior menu.

Power BI As-Timeline visual

You can view the tooltip by hovering the mouse over any data point, and you would be able to see the attributes in the tooltip as shown below.

Power BI As-Timeline visual with detailed data when hovering your mouse over a data point

The seasons represented in the visual does not show the number of episodes. You can overlay data on the top of the data points. This can be very useful in cases when one is looking for a specific data point. To overlay data, add the episodes field in the Overlay section, and the episodes would be shown on each data point as shown below.

Power BI As-Timeline visual with the episodes field in the Overlay section

Finally, if you analyze the X-axis only 4 data points are shown in a duration of 15 years. In time series analysis, at times it becomes necessary that the axis is comprehensive. Change the X-axis labels to 15 from the setting of 5, which will change the visual as shown below.

Power BI As-Timeline final visual

In this way, you can use the "as Timeline" visual for different types of time series analysis and make quick derivations from the data.

Next Steps
  • Consider trying out this visual to perform the time series analysis and explore a variety of configuration options to fine tune the visual to make it suit the requirements.


Last Updated: 2019-06-20


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Comments For This Article




Wednesday, July 15, 2020 - 5:22:46 AM - ELIAS ELIAS Back To Top (86137)

Siddharth, can you please share the data file, i googled but was not able to find these datasets, thanks in advance


Tuesday, May 26, 2020 - 4:59:10 PM - ELIAS ELIAS Back To Top (85770)

Can you please let me know where I can find the dataset to follow with your article


Friday, April 10, 2020 - 3:44:26 AM - chethanrao Back To Top (85324)

Hi there,

I have a query in power Bi. I have creaed one Bar chart visual tat shows month's for the selected year in the filter and the sales value. I requirement here is when i click on any one month from the bar the color has to change on my click. Any help will be appriciated. Thanks!


Thursday, June 20, 2019 - 9:49:21 AM - Adrian Hernandez Back To Top (81527)

Thanks for sharing. Very useful. Is it possible to insert a trendline on each series?



download


Recommended Reading

Power BI Incremental Refresh for SQL Sources

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Create Power BI Connection to Azure SQL Database

Using Parameters in Power BI





get free sql tips
agree to terms


Learn more about SQL Server tools