Visualising trends over time helps us make decisions – for instance by telling us which marketing campaign was the most successful, getting a sense of what the future might look like, or to see where profit can be maximised as an organisation scales.
This short post will explain the key methods for displaying changes over time in Power BI.
Getting dates into the right format
Date data is fantastic, and Power BI handles it well by default. Before progressing you should be familiar with the following practices:
- In Power Query, transform any date/time/timezone columns to ‘Date’ if the time and timezone aren’t useful to improve your report’s performance
- Where your report has more than one table with a date column, add a ‘Date’ table to your report. Creating a date table
- Where your ‘date’ column is a UNIX timestamp it will need to be transformed. Convert UNIX timestamp.
Visualisations to show metrics over time
The most popular visualisation to use to show trends over time is the ‘line’ graph; however depending on your needs the following can also be useful:
- Column graphs can be easier to see differences between fewer time blocks – three year periods for instance
- Area charts can be a useful variation on line graphs where a running total is being shown
- Gantt charts are great for visualising project work over time
- A clustered column or histogram showing resource availability in each time period, aligned beneath a Gantt chart, can show peaks and troughs and allow the smoothing of resources accordingly
Building your Visualisations
Sum or Average over time – the simplest graph to generate, it displays the actual figures for a particular date. Pick the line graph visualisation, drag your date field into the x-axis and the value you want to track into the y-axis and the trend over time will display.
Aggregated over time – Similar to the above, only adds the numbers from the previous time period to the next. Add a ‘quick measure/running total’ measure to your report and use this for your y-axis. You might consider using an Area graph as an alternative to a line graph for this purpose.
Sometimes the structure of our data doesn’t make visualising a running total over time easy however. Let’s say you have the following data set, and want to show the growth of membership over time:
| Name | Member | Joined | Left |
| Jane | 1 | 20/02/2020 | |
| Peter | 1 | 25/04/2021 | |
| Jack | 0 | 23/07/2020 | 18/09/2022 |
| Gertrude | 1 | 23/11/2022 | |
| Robin | 0 | 20/12/2020 | 18/03/2021 |
A running total on the ‘Member’ column will be inaccurate, since the line will only ever go up despite people leaving. A simple way to achieve the desired result can be done in Power Query:
- Create a duplicate of the table
- In first table, filter to Member=0, replace 0 with -1, remove the ‘Joined’ column, rename the ‘Left’ column to ‘Joined’, and disable load
- In the second table replace 0 with 1 in the Member column, remove the ‘Left’ column, and append the first table to it. It should look something like this:
| Employee | Member | Joined |
| Jane | 1 | 20/02/2020 |
| Peter | 1 | 25/04/2021 |
| Jack | 1 | 23/07/2020 |
| Gertrude | 1 | 23/11/2022 |
| Robin | 1 | 20/12/2020 |
| Jack | -1 | 18/09/2022 |
| Robin | -1 | 18/03/2021 |
Our running total line graph should now dip where members have left.
This can also be achieved without any data transformations using DAX as outlined in HR Analytics – Active Employee, Hire and Termination trend. I prefer the simpler solution above however.
Gantt charts – Microsoft provide a free minimal Gantt chart as a custom visualisation. The chart lacks more advanced features – like dependencies – which are available in MS Project. Building a Gantt Chart using Power BI. Numerous paid-for Gantt charts are also available from the custom visual store.
Histograms for Project Management – When set beneath a Gantt chart, a histograms are invaluable to understand where we can expect peaks and troughs in work to occur – and potentially shuffle tasks around to better allocate project resources. To do so, we need to understand how much resource is available across the project, how much resource each task within the project is expected to take, and the boundaries of time each task has. Once we have that data, we can use an ‘Events in progress’ calculation to build a histogram using the clustered column visualisation. See Events in Progress