show all months on graph even with no data

[sy898661]

Hi all!

I am trying to recreate an excel graph in PBI and am having some issues... 

Is it possible to have this graph in PBI:

look like this grpah in Excel:

like how it has the blank space were there is no data rather than just skipping to the next data-filled month?

I tried changing the x axis to continuous and manually seting the start and end date but that didnt work, and I also tried to add a date table and put 0 as the value for the non-data-filled months but that didnt work either.

If anyone has any ideas please let me know 🙂 thanks!!

[v-yingjl · Accepted solution]

Hi @sy898661 ,

You can try to put month hierarchy in the x-axis field and enable 'Show items with no data' option to achieve this:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

You saved my life. Thanks a lot!

[lovishsood1]

I tried this but, when I take calculated column

Calculated Column : Format(Date, "MMM")
then for this It does not show any data for Blank months.

[GunnZ]

Hi,

Sorry to dig this post up, but i have a similar issue and while your solution helped me display the months with 0 data inputs, it also disabled my set filter.

In my case I have data from the last couple of years, but only want to show the latest 12 months. Now I get everything before that AND all upcoming months of this year as well.

Is there a way to filter those again?

Thank you!

[Veysel]

I have the same problem.

when I choose show items with no data, my 12 months filter doesn't work. Does anyone now how to fix it?

[sy898661]

@v-yingjl I added in dummy data-lines where there was no data for the dates we needed spaces in and tried this and it worked!! thank you so much!!

[Anonymous]

Having to put a lot of zeros in the excel tables were so frustrating, now this saved my life, I am so relieved

[amitchandak]

@sy898661 , With the visible viusal, I doubt. But this how it can happen if you want a break in between.

1. Make the value for those months as blank, not 0. If(Max(Table[Month] in {"Apr20","May20"}, blank(),[measure])

2. Make the type as Categorical

If it does not resolve. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lbendlin]

I don't see what's wrong with the Power BI picture. It is a representation of your data with a high level of truthiness.

[sy898661]

@lbendlin The two areas are separated in the excel graph because they are two different campaigns that happen to fall in the same year. My boss is looking for separation. I know it technically portrays the same information but its not what I am looking to do....