Column legend filtering X-axis

[Anonymous]

Hi guys,

I have a report combining 2 fact tables. One table with actual sales and one with forecast sales. The actual sales table extends to the current month (so March 2023 as I'm writing this), while the forecast extends to the end of the year.

I have created a line and stacked column chart to let me view the actual sales and forecast sales together. I want to see the historical actuals in the bars and the forecast in the line, which works below.

However, when I add a legend in the column legend section, in this case the product category, the future months are filtered out and the forecast line only extends to March (the current month), rather than seeing the future months, even though my date filter is still extended to the end of the year. 

Does anyone know why this would happen? I can't think of a reason why the product category would filter the date range.

Any help would be much appreciated.

Many thanks,

Paddy

[lbendlin]

Please show your data model. Usually these two fact tables should not be connected. Instead, they should be controlled by a common Dates dimension table.  The X axis on your visual should be fed from that table too.

[Anonymous]

Hi @lbendlin thanks for getting back to me.

Below is the data model. The two fact tables aren't connected, and they are both connected to the materials (products) table and calendar table as you'd normally expect, so I can't see anything in the data model which would cause this.

Any ideas?

Many thanks,

Paddy

[lbendlin]

agree, that looks benign. And you confirmed the x axis is fed from the Calendar table?

Would you be able to create a sample pbix that illustrates the issue?

[Anonymous]

VolumesReport copy.pbix

I'm unable to upload pbix files unfortunately but hopefully the above link to the OneDrive file will work for you?

I'm starting to think this is just a strange feature of the Power BI visualization rather than any modelling issue, because underneath the cluster column/line chart visual which doesn't show the future months, I have put a Matrix with exactly the same fields and slicers, and the future months forecast is shown. This seems to show it's just a strange quirk of the visual, but hopefully I'm wrong!



Many thanks,

Paddy

[lbendlin · Accepted solution]

While I agree that this can be considered a bug there is a workaround. See if you can spot it.

see attached.

[Anonymous]

@lbendlin How interesting, I did try the 'show items with no data' option but it didn't work with the automatic date hierarchy.


Below, with a date slicer on 2023 dates and using the 'show items with no data' option, all dates in the calendar are shown going up to 2026 despite the slicer.

But, as you have done with your file, this option does work when you use the date hierarchy I created myself as shown below.


I can't say I quite understand why this would be the case, but I'm glad to have the solution!

Thanks so much for the help on this, stopped me from tearing my hair out.

Cheers,

Paddy

[lbendlin]

In most cases you want to avoid the auto date/time feature. It can have massive side effects.