Hello everyone,

As mentioned in the title, in this blog I want to show you how to avoid scrolling the x-axis horizontally especially when you only have a limited space for your charts like the photo shown below. I met this problem while creating a sales dashboard and wanted to show the key metrics along with a line chart showing its changes over time. As you can see, all of the line charts require to be scrolled to the right to see all months values. The reason behind this is because when you create the line chart and take the Month column from your Date table for the x-axis, each data point for the months now is treated as one category (discrete). To fix this problem, we want to make it continuous so that our x-axis will be shown fully without the need of scrolling horizontally.

hhh

After looking up on the community, I realised a lot of people also encountered the same problem and not many could give an easy and straightforward solution. At the end, I finally came across a Youtube short clip and found it very helpful. If you prefer to follow the instruction by watching the clip, you can find it here. Otherwise, you can follow the steps I’m outlining as follow:

hhh

Step 1 – Create a new table (New table)

hhh

Step 2 – Type in this following DAX formula:

Month column =
ADDCOLUMNS(
    CALENDARAUTO(),
    “MonthYear”, EOMONTH([Date],0)
)
hhh
To explain this formula, this new table will have 2 columns, one column contains a set of date generated automatically based on the data in the model (created by the CALENDARAUTO function), the other column named “MonthYear”, created by the EOMONTH function where every date in the Date column will be set as the last date of that month (for example, 01/01/2019 will be set as 31/01/2019).
hhh
jjj
Step 3 – Create a relationship between the Date column in the table that we’ve just created with the date field in your dataset, in my case it is the Order Date.
hhh
hhh
Step 4 – Create a line chart that uses the “MonthYear” for the x-axis. This x-axis now is continuous so all of the month values will be shown regardless of the size of the chart. The final look of the charts should look like this:
ii

h

And that’s all. I hope you found this helpful. See you in the next blog !

 

 

The Data School
Author: The Data School