The line charts in Power BI are a useful visualization tool to display events happening over time. However, line charts return optimal results when used at the day granularity. To obtain the best visualization at other levels of granularity, it is necessary to apply changes to the data model and to write a DAX expression. In the following sections you will see how to:
Relationships in Power BI are limited to single columns, but whilst this seems like a major limitation there is actually a simple solution to create a relationship with multiple columns in Power BI. To create a relationship with multiple columns in Power BI we simply need to create a new column by merging the required columns together. Using Merge in Power Query gives you the ability to join on a EQUAL join with one or more fields between two tables. However, in some situations you need to do the Merge Join not based on equality of values, based on other comparison options. One of the very common use cases is to Merge Read more about Dates Between Merge Join in Power Query.
Offset columns are representing the difference of that date (in days, months, quarters, and years) to today’s (day, month, quarter, and year). For example, if today is the 8th of May 2020. The Day offset for today is 0, for 7th of May 2020 is -1, for 6th of May -2, etc. This video explains why and how to add a relative month column to a Power BI calendar or date table. The example presented is for a calendar table that is c. There are multiple options here. Year provides the number of year (2017), Start of Year first day of year (1.1.2017) and End of Year, surprisingly, last day of year. Similar to previous, with additional option Name of Month (March, for example.) Quarter. Similar to previous.
Before analyzing the solution, we need to clearly state the problem. In the model used for the demo, the Date table contains a hierarchy with Year/Quarter/Month. If you build a line chart with Sales Amount and put the hierarchy on the axis, you obtain the following result.
In the sample model, we use a custom Date table. However, if we had used the Auto Date/Time option in Power BI, the result would have been almost the same. Indeed, the presence of a hierarchy in the Axis property of the line chart makes Power BI show the first hierarchical level, which is the Year. As a result, the chart only contains three points, whereas users likely need more points to be able to draw any insights.
This is how the properties would look, when you use a custom Date table.
By pressing the Expand Next Level button highlighted in the next screenshot, you can navigate to the next level in the hierarchy applied to the Axis property.
This action shows data at the Quarter level, which in this model is defined by using a string that includes both quarter and year, in turn making the quarter column unique for different years. The result seems redundant on the X-Axis, because the year is repeated twice for each data point.
By repeating the Expand Next Level operation, we navigate to the month level of the Calendar hierarchy.
This visualization is confusing, because the year is repeated three times per data point label on the X-Axis. However, using quarter and month columns that only display the name of the quarter or month without the year would not entirely solve the problem. The year would still be duplicated for every point of the chart.
A partial workaround is to disable the Concatenate Labels property in the X-Axis section of the Line Chart visual, as highlighted in the following screenshot.
The result shown in the next screenshot is only marginally improved. Removing the year from the Quarter and Month level may improve the reading experience, but it is hard to think how to display more than 3 years in the same chart. Indeed, Power BI shows a label for each point creating a scrollbar that only displays part of the report when there are too many labels. (The next chart still has the year number in Quarter and Month level, though).
The last visualization displays 36 points. Increasing the number of years would make it unreadable in a single visualization. Moreover, expanding the chart to the week level would require the display of 52 points for each year, generating the same issue again: too many points, too many labels.
All the line charts used so far use the Categorical type, because the data is displayed using strings. The line chart offers an alternative visualization type called Continuous, which requires a number or date column for the Axis property. The Continuous visualization type displays a smaller number of labels for the Axis, because missing labels can be inferred by the distance from the existing labels. The Continuous visualization also features a special management of date columns, displaying a simplified Year-Month label. For example, the following screenshot displays the same measure as before, this time using the Continuous visualization type. The distance between labels can be inferred by the gridlines, which are applied to each visible axis label.
The Type property in the X-Axis area of the Line Chart properties can be set to Continuous because the Axis now has the Date column from the Date table, which is a Date data type. Only dates and numbers can be used with the Continuous visualization type. The Gridlines property is also enabled, and it is part of the same X-Axis area.
Continuous line charts improve the handling of labels, but we have been forced to use a daily granularity. This results in 1,000 points included in the line chart, but most importantly this might not be the granularity that the user wants to display. Better insights might be obtained by using a monthly or weekly grain. In the next sections, we show how to overcome this limitation by modifying the data model using DAX code.
The Continuous type of a line chart is only available for numerical columns. A date column is internally managed as a number and the Line Chart visualization also displays the dates in a smart way, as you have seen in the previous section. The Continuous visualization removes the need for a horizontal scrollbar in case there are too many points in the X-Axis, compressing all the data points within the same visualization. This behavior can also be useful when displaying data at a different granularity, such as months and quarters.
In other words, you need a Date column on the axis to use the Continuous visualization. Yet, with a lower granularity such as month or quarter, the column used to slice is typically a string, representing that month or quarter. In this scenario, it seems like you cannot use the Continuous type and you are forced to use the Categorical type (with all its constraints), but this is not true.
The way to unlock the Continuous type for different time periods is by displaying the desired granularity using a Date column that aggregates the entire period into a single date. The more intuitive approach is aggregating the entire period on the last date of the period itself. For example, we can create the following two calculated columns in the Date table.
We intentionally used two different techniques. The Year-Month calculated column computes the end of the month using the DAX function. This can work for any standard calendar. However, for custom calendars or for a granularity other than month, the technique used here to compute the Year-Quarter calculated columns is always valid: just use , keeping in the filter context one or more columns identifying the period required. It is always a good idea to use a single column representing the required granularity. However, if the quarter were not unique for every year we would not have had the Year Quarter Number column; ( ‘Date’, ‘Date'[Year], ‘Date'[Quarter Number] ) would have been required.
The following screenshot shows the Continuous visualization using the Year-Month column in the Axis property. We also enabled the Data Labels and X-Axis / Title properties for this chart.
By replacing the Axis property with the Year-Quarter column, the granularity of the chart is quarterly. The only caveat is that by default in this case the tooltip will display the date used (end of quarter) as a label in the tooltip itself. A workaround is using the display format MMMM yyyy for the end of quarter and end of month columns. However, the end of the period would not clarify whether the granularity is by month or by quarter, therefore it is important to display the granularity in the Axis enabling the Title visualization, which we also enabled in the previous Line Chart computed using the month granularity (the following screenshot does not apply the MMMM yyyy format, which is used in the sample file you can download).
As you have seen, using a date to represent months and quarters proves to be a useful trick to unlock the Continuous visualization type. The next step is displaying data at the week granularity, as described in the next section.
Because weeks do not align with months, quarters or years, we need a week granularity column grouping all the days within the same week. We create two calculated columns in the Date table:
The result shown in the following screenshot uses the Week column in the Axis property.
When displaying data at the week level, there are 52 data points for every year. The previous chart has 156 data points, so it could be useful to restrict the date range of the chart. However, if the calendar is not a week calendar such as a standard ISO, then the data of the first and last week of the selected time period might be incomplete. The next section describes how to manage this condition in a standard calendar.
A date selection might include incomplete periods of time in the line chart, resulting in a poor visualization. For example, in the following screenshot we selected sales between February and June 2008. The first and last weeks in this range are incomplete, showing a small number relative to the rest of the line chart – the value seems to be below the minimum value set on the Y-Axis (1,000,000).
Showing the data in a matrix visualization provides more details about what is going on.
The first line pertains to the week ending on February 2, so Sales Amount only includes two days’ worth of sales (February 1 and 2) disregarding any sales occurring on any of the other five days that week (January 27 to 31). Same with the last week, which includes sales data from June 29 and 30 but does not include sales for the remaining five days that week (July 1 to 5). This also explains why the report includes a week ending in July 2008 although the Month slicer only includes dates up to June 2008.
We can create a measure that removes incomplete weeks from the calculation, as shown in the following code. A similar technique could be used for incomplete months and quarters.
By using the new measure, the Line Chart visualization now removes the first and the last data points, showing more reassuring figures.
The behavior of the “Sales Amount complete weeks” measure is clearer when comparing it side-by-side with the Sales Amount measure in a matrix visualization.
The new measure returns a blank value for the two incomplete weeks. Moreover, the filter technique used in the “Sales Amount complete weeks” measure also works at the Total level; it only includes the complete weeks without requiring an expensive iteration over weeks to obtain the correct result. This result is useful to compute an average by week, which would otherwise be polluted by the presence of incomplete weeks if one were using the regular Sales Amount measure.
This article described several techniques to improve the visualization of measures in a Line Chart using the Date table at different granularities in the X-Axis. These techniques can be adapted to any non-standard calendar. The important takeaway is that by using DAX and by applying small changes to the data model, it is possible to overcome certain limitations of the existing visualizations.
In this post, we will go through how to create a Power BI chart that compares numbers each year vs each previous year. We will use sales data as an example.
First, let’s connect to our data from Power BI Desktop. We will use the sample data from here and connect as in the example. This data holds 3 years of Orders data.
Once connected, create a simple date table that holds dates between the year ranges.
Now let’s build our report. First, a simple view of our data. We can see we have orders from 2017 to 2019:
Let’s create a measure called Total Sales. This displays the total of all sales based on the context:
Now let’s use SAMEPERIODLASTYEAR to create a measure for the total sales this year:
If we swapped the day for YEAR-MONTH, we can see we are now comparing the YEAR-MONTH periods. For example, in January 2018 there was a total of 320 in sales. In January 2019, the total was 100 and Total Sales Last Year is displaying the 320 correctly:
From here, we can create our column chart showing the comparisons of year vs previous year:
As 2017 did not have previous year data (i.e. maybe it was the first year of business), we may want to exclude it. Let’s remove it by filtering the visual, and change the chart to a Line and Clustered Column Chart, which can also easily allow us to compare periods:
Note using SAMEPERIODLASTYEAR is similar to using DATEADD, e.g.
And if you want a measure to see the total sales this year, you can use:THANKS FOR READING. BEFORE YOU LEAVE, I NEED YOUR HELP.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.CARL
ABOUT CARL DE SOUZA
Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.carldesouza.com LinkedIn