When you’re using Power BI, you probably create line charts that show data by month pretty often. It usually works great, but what happens if some months don’t have any data?
By default, Power BI just skips those months in the chart. That might seem okay, but it can actually be misleading—especially if you’re looking for trends over time, including the months with no activity.
The Problem
Let’s say you have a line chart where the category is "Months" and the value is a measure counting certain records, like this:
If there is no data for a particular month (e.g., July), that month won't appear in your chart.
An easy solution is to use the "Show items with no data" option for your Months category. However, this approach has a few downsides:
Disrupted Trend Line: When you enable "Show items with no data," the trend line in your chart will appear to ‘stop’ before the month with no data and then continue only at the next month with data. This creates a visual gap, which can be misleading because it suggests that there was no continuation of the trend during that period, when in fact, the trend might have just leveled off or stayed flat at zero.
Inclusion of Future Months: Additionally, this option will display all months, including upcoming future months, which might not be desirable. You want to ensure that your chart only shows up to the current month, even if there’s no data for some months.
The Solution
One effective way to handle this scenario is to adjust your measure and apply a filter at the visual level to ensure that future months are excluded from the chart. Here’s how you can do it.
Revise the Measure:
Modify your measure to explicitly handle months with no data. You can do this by simply adding 0 to the measure, like this:
You can also revise the measure to include a conditional check using ISINSCOPE, which determines if the months is within the scope of the visual. If it is, but there's no data, the measure returns 0 instead of not showing the month at all. This approach works well when you’re working with hierarchies. Here’s the revised measure:
This measure ensures that each month appears in the chart, with months that have no data showing as a line with a value of zero. This continuity prevents the trend line from appearing broken, making it easier for users to follow the data flow over time.
2. Apply a Visual Level Filter:
Now that your measure ensures all months are displayed, the next step is to exclude future months from your chart. You can do this by creating a simple offset column for the Month column and then applying it as a visual-level filter on your chart.
Create an Offset column. There are several ways to create this offset (best practice will be to create it in Source). To simplify the solution, we’ll create it in Power Query with the following script:
Drag the ‘CurMonthOffset’ column from your Date table into the Filters pane.
Set the filter condition to only include months that are less than or equal to the current month.
This will ensure that only the months up to the current one are displayed, without showing any unwanted future months. This approach keeps your chart clean and focused on the relevant time frame.
Final Result
Final Thoughts
By revising your measure and adding a simple visual-level filter, you can easily control how missing months show up in your Power BI line charts. This way, your charts stay clear and accurate, showing all the relevant months without cluttering things up with future dates.
Not only does this make your reports easier to read, but it also makes sure your data is presented in a way that makes sense. So, next time you notice some months missing in your Power BI charts (or any other hierarchy), give this approach a try and see how much of a difference it makes.
Comments