Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

No Data showing Gap in line chart

Hello Friends, 


Another problem. I have a series of % data associated with dates. When showing on a line chart it will show gaps where no data is available. 


-X axis is month field from a date formatted column "Submit Date"


-I cannot select continous on the X axis options as it flips back to categorical.


the plotted data is a measure as follows. 


Cumulative % =
var compliant=CALCULATE(DISTINCTCOUNT(Details_All[IDNumber]),Travel_Details_All[Compliant]="YES",FILTER(ALLSELECTED(Details_All),Details_All[Submit Date]<=MAX(Travel_Details_All[Submit Date])),VALUES(Travel_Details_All[Submit Date].[Year]))


CALCULATE(DIVIDE(compliant,DISTINCTCOUNT(Details_All[IDNumber])),FILTER(ALLSELECTED(Details_All),Details_All[Submit Date]<=MAX(Details_All[Submit Date])),VALUES(Travel_Details_All[Submit Date].[Year]))


What could be the problem?




Advocate II
Advocate II

Anyone reading this please note the if function doesnt work if you are page filtering the date (say to last 12 calendar months)... which for a rolling 3 year data set to just show a rolling 12 months would be perfect (so you can have pa line for current year and previous year), however when using medians and averages and say a "last day of the week" graph like this below you will get the occasoinal day with no data - and so even though days either side of the last day of the week day showing has data in the average - and that shows in the average pop up table... it wont show the day... and adding an IF clause to add a zero or any number, wont work as will spread the dates out to the full 3 years... 


IF measure for the last person asking:


[Measure being used orignally]


, [measure being used orignally])


PS: using +0 in or at the end of the measure wont work as will also push all the dates out to the start/end of the date table being used....

#Still a problem Microsoft!!

Community Support
Community Support

Hi @Mike22 


Some possible workarounds:


1. Change the X-Axis type to Categorical instead of Continuous. Dates without data won't show on the chart, but the downside is it's not obvious that any dates are missing, since they're all spaced equally without gaps.

2. Use a column chart instead. The gaps in data will show this way. If you still want a trend line, you can use the "Line and stacked column chart" and put the measure you want as both a line and column. Format the column color (under Data colors > Default Column Color) as something unobtrusive. You'll at least have a visual indication that there is no data if there is no bar, and you can see the line for trending.

3. Set your measure to return 0 for nulls. This will actually plot a 0, which could be really bad depending on your dataset. Don't want to confuse it with real values of 0 as opposed to gaps. But if it makes sense for your dataset, it makes the gap line up with the axis and is almost the same as not being there at all. Maybe even set it to -1 if you could have actual 0 amounts but not negative numbers in your data.


Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more

The problem with all your solutoins is that the end users want it to look the same throughout - this to me is more of a bug as only breaks once published in the cloud...

The only work around I can find is add a hidden column and make it white, and reduce spacing to zero...however it ruins the look of the gridlines.... now if I could move the grid lines forwards and back.... 😐


Thanks for your responses though - good to see support for this ongoing issue.... a lot of people - myself included were fine with adding +0 or using IF( loops to resolve issues visually, but I've come to the end on this one - I truely believe its a bug...


My reason for the bug is that lets say the line dissappears for the median on weekday (as start date) 05/02/2023, but only when published... on the desktop version it shows the rate for the days before then 01/02/2023/ + 02 + 03 + 04 but, when I publish it, as there wasnt anything on 05/02/023 it decides not to show.... I can demonstrate to my colleagues, but difficult online... and time-consuming... 

I'll see what Microsoft has to say about it..


Clin Epi

dear @v-diye-msft 

Thanks for the reply. 
I would not mind using option 1. The problem is that in this sample the X axis is already set to categorical and keeps leaving the gap. I cannot select continuous as it defaults back to categorical. 

Is there a work around? Thanks!

Hi @Mike22 


Would you mind sharing your dummy pbix here? that I can check it further.

Please make sure you've removed the confidential info.


Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more

@v-diye-msft  The dataset is very big but I am trying to put it together. I think I understood where the issue might be.


For the specific month where the blanks are there is no data at all in the dataset. So the date doesn't exist at all in the dataset.

I basically would need to tell the measure that it should use the complete calendar and if there is no value for a specific day to use the value of the previous day. This should allow the line to be straight and continuous  until a new value makes it change. 


How can I achieve this measure?


Hi @Mike22 


If you'd like to keep the line to be straight and continuous even the actual value is null, that means the specific day will take the last valuable value rather than no value. that is different from the actual data, do you mean it? Yes, it can be achieved by measure using IF function. 


Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more

hi @v-diye-msft ,


This is exactly what I want. If there is no data for the day it should show the data from the previous day. Until the value changes of course. 

Can you guide me on how to do it with an IF?

Hey Mike, I am after exactly the same thing. Did you get an answer on this?



Good morning.


I don't want to open a new thread.


I am facing the same issue. My chart is showing gaps in the line because my data set doesn't have info for these dates.




How can use the IF function to solve it?

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors