Removing gaps in Line and Stacked Bar chart from weekends

[PerDiem]

Hi all,

I have a chart that is displaying all data correctly, but is inserting values in the X-axis due to no data existing on the weekends. I would like to close those gaps but maintain all other formatting for the chart. I have seen some suggestions directing to use an index for the weekends but this cause my x-axis values to display differently and ideally I would like to maintain the current formating of MMM-YYYY. How would you go about solving this issue? Thanks in advanced.

[Anonymous · Accepted solution]

Hi @PerDiem ,

Please try:

(1)Since the value is empty, there are no results, in which case going to the chart's options and selecting Show items with no data should return what you need:

(2)Or create a new table and a measure then set the newtable[x-axis] as the X-axis of the Line and Stacked Bar chart. Refer this :Remove gaps in stacked area chart - Microsoft Power BI Community

New Table = VALUES('Table'[Date])

Measure =
VAR _s =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = MAX ( 'New Table'[Date] ) )
RETURN
    IF ( ISBLANK ( _s ), 0, _s )

(3)Or you can create a column to change the date format and then place the column on the x-axis.

Column = FORMAT('Table'[date],"mmm yyyy") 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[mj2024]

Hi @Anonymous 

I am facing the same problem.  I am trying solution 2.  In my case, my table has 3 columns - one is a date column and the other 2 are values.  One of these 2 columns is an index column that I use to link other tables so there is a 1-1 relationship between the tables.  I need all three in the new table.  How do I update your solution to fit three columns?  I am new to PowerBI so sorry for the rookie question.

Thank you

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[mj2024]

Hi @Ashish_Mathur 

Here is some sample data.  I had to add an extra column so the values are legible but you can remove the extra empty columns to get the data set. I need a bar chart in PowerBI.  If you plot this data you will get gaps in the x-axis in PowerBI.  I do not want any gaps so each bar is displayed next to each other as if there are no gaps.  Does this make sense?

SYMBOL	TRADE_DATE		METRIC1		METRIC2		INDEX
ABC	2024-12-13		20177833		9024939366		ABC2024-12-13
ABC	2024-12-16		23598834		10656997446		ABC2024-12-16
ABC	2024-12-17		22733494		10331463683		ABC2024-12-17
ABC	2024-12-18		24444545		10691799538		ABC2024-12-18
ABC	2024-12-19		22963681		10035817507		ABC2024-12-19
ABC	2024-12-20		64263694		28057528800		ABC2024-12-20
ABC	2024-12-23		19152519		8336133895		ABC2024-12-23
ABC	2024-12-24		7164543		3147598676		ABC2024-12-24
ABC	2024-12-26		8199927		3592470018		ABC2024-12-26
ABC	2024-12-27		18117713		7800218978		ABC2024-12-27

Thank you

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.