By Meeta Gargav
We came across an unusual situation in which incorrect years (2000 and 2500) were shown in the X-axis of a Power BI report. Our questions about this issue weren’t specifically answered no matter how much Internet searching we did. We did find a workaround, and we’re describing it in this blog post in the hope that it saves someone else tearing their hair out!
Incorrect Years 2000 and 2500
We created a Power BI report using data from the Act! Opportunity module. This simple report shows the count of opportunities won (Y-axis) over the time period (X-axis). The slicer in this report is the actual close year. The report works as expected when we select multiple years.
However, we noticed issues with the report when we selected only a single year. Instead of the selected year (2015 in the example), the X-axis displayed the year 2000, which we did not select, even though it displayed the appropriate data for 2015. It also added 2500, a year that did not correspond with any data, and that did not even appear in the list.
We get quite a few hits when we run a search on power bi displaying the wrong year. Some of them are listed at the end of the post, but my workaround is different from the recommendations I found on Google.
When the issue is present and we select X-Axis in the Format section, we notice the Type drop-down is set to Continuous. Type allows us to choose either Continuous or Categorical.
Most of the resources I found on the Internet say that in a report like ours, the Year should be set to Continuous and the Scale Type to Linear. I found that this setting didn’t work in the case at issue. But when we change Type to Categorical, the report appears as we expect.
Here’s a closeup of the corrected report:
The other workaround that I have found is to create another Year column and make it a text field. We would then use the decimal Year for the slicer, and the text Year for the X-axis. However, the reason I don’t use this as my default is that we would then be bulking up our Power BI report with extra columns.
I am really interested in what you think of my workaround, so please leave a comment.
Here are a few of the search results that I examined: