Line chart broken

[atoice]

Hi Power BI Community,

Hoping it's a quick / simple fix. I have data from 2023 through current year. I am getting breaks in the line(s) when I add the 2023 data. When using only 1 year, no broken lines. When I use 2024 and 2025, no broken lines. Any chance you know what I'm doing wrong? I made a new calendar and not use the old one. I'm guessing the sales data has an issue. Or is it because of a leap year? I see the one week is out of order for March.

Your thoughts are appreciated.

TIA

[v-tsaipranay]

Hi @atoice ,

Thank you for reaching out to the Microsoft Fabric Community. Also thank you @SolomonovAnton  for your input.

Based on your explanation, the issue appears to be calendar misalignment, specifically how week-ending dates are handled differently in 2023 compared to 2024 and 2025. Although your data is complete, inconsistent week-to-month mapping is causing the line breaks.

To fully resolve this:

• Please ensure your Calendar table consistently aligns week-ending dates across all years.
• Verify that your X-axis field is sourced from the Calendar table. This will enable the “Show items with no data” option under Visual >Format > X-axis.
• Optionally, update your measure to handle missing points:

Sales Line = COALESCE(SUM('Sales'[SalesAmount]), 0)

Your workaround using a continuous X-axis is a good interim solution, but correcting the calendar structure will permanently fix the line breaks.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[atoice]

Hi V-tsai,

I did try to add the formula you had. it did put zero line in. But it was still broken. It definitely is the calendar piece. But I did attempt to manually change "calendar month" to March, but thinking it's maybe because the week ending is in April. I did the same with the other breaks in the line to align the same months for those weeks. But it still didn't do it. It's something in how the calendar is aligned. I can't attached the file, or I would.

I appreciate your additional help.

[v-tsaipranay]

Hi @atoice ,

Based on what you described, the line chart breaks are due to how the calendar assigns weeks to months especially in cases where a week ends in a different month than most of its days.

To resolve this, I recommend reviewing your calendar logic to ensure that weeks are consistently attributed to the correct month either by using the week’s start date or basing it on most of the week’s days.

Avoid manually changing month labels, as this often leads to misalignment. Instead, define clear logic in Power Query or DAX when building your calendar. Also, make sure your chart uses the Date field from the calendar table as the X-axis and that the relationship to your fact table is properly set.

Enabling “Show items with no data” and wrapping your measure in COALESCE([Measure], 0) will help maintain line continuity once the calendar alignment is fixed.

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

Thankyou.

[atoice]

Hi v-tsai,

Thank you again for follow up. I don't know how to make a calendar in Power Query / Dax. I did use the formula, but it didn't work because of the calendar. In both ways, modifying the calendar to adjust  for weeks 13, 26, 35, and 48 did not work. I can't paste the rows since it tells me I am over the character limit. I'm going to leave it as it with the team as that layout was the way they had it (in Excel). I just wanted to make it a little cleaner. I tried to take some snips with weeks in question (2023) and the comp week 13 for 2024.

[v-tsaipranay]

Hi @atoice 

Thank you for the update, and I completely understand that adjusting weeks and months manually especially when dealing with edge cases like week-ending dates can be time-consuming.

To fully resolve the line breaks and ensure consistent plotting across all years, the most reliable approach is to use a dedicated Calendar Table in Power BI with consistent week and date alignment.

Since you mentioned you're not familiar with building one, here’s a ready-to-use Power Query script that you can paste directly into Power BI to generate a proper calendar:

To get started, open your Power BI Desktop file. Then, go to the Home tab and click on Transform Data this will open the Power Query Editor. Inside the editor, click on New Source and choose Blank Query. After that, open the Advanced Editor and paste the calendar script provided into the window. Once done, click Done and then Close & Apply to load the new calendar table into your model.

let  
    StartDate = #date(2023,1,1),  
    EndDate = #date(2025,12,31),  
    DatesList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),  
    TableDates = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Calendar Date"}),  
    AddYear = Table.AddColumn(TableDates, "Year", each Date.Year([Calendar Date])),  
    AddMonth = Table.AddColumn(AddYear, "Calendar Month", each Date.ToText([Calendar Date], "MMM")),  
    AddMonthNum = Table.AddColumn(AddMonth, "Month Number", each Date.Month([Calendar Date])),  
    // Calculate Week Number based on ISO 8601 (week starts Monday)  
    AddWeekNum = Table.AddColumn(AddMonthNum, "Calendar Week", each Date.WeekOfYear([Calendar Date], Day.Monday)),  
    // Calculate Week Ending (Sunday)  
    AddWeekEnd = Table.AddColumn(AddWeekNum, "Calendar WE", each Date.EndOfWeek([Calendar Date], Day.Sunday)),  
    // Optionally add Month Quarter  
    AddQuarter = Table.AddColumn(AddWeekEnd, "Calendar Month.Quarter", each "Qtr-" & Text.PadStart(Text.From(Date.QuarterOfYear([Calendar Date])),1,"0")),  
    // Sort by date if needed  
    SortedTable = Table.Sort(AddQuarter,{{"Calendar Date", Order.Ascending}})  
in  
    SortedTable

After pasting the script, click Close & Apply to load the calendar. Then go to the Modeling tab, select Mark as Date Table, and choose the Calendar Date column. Lastly, link it to the date column in your sales table to ensure your visuals display correctly.

With this structure in place, your visuals should automatically align weeks consistently across all years, eliminating the breaks you're seeing especially in weeks like 13, 26, and 48.

Also, if your current layout is already accepted by your team and serves the intended purpose, your existing solution (continuous X-axis with small gaps) is perfectly valid.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[atoice]

Hi V-tsai,

That is crazy impressive with making a new table! Thank you for your consistent help. I did use that and connect, but it still shows the break. It must be in the data file. I took snapshots of the relationship from the data file to the new calendar you built. It still shows the same as when using my calendar.

I did try using other columns for the X-axis as well as legend, from the data file and leaving your calendar date. But I haven't found the right combination to align the line graph? Let me know your thoughts when you have time. 

Once again, thank you for the continued help. That is really awesome how you built the Query.

[v-tsaipranay]

Hi @atoice ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[v-tsaipranay]

Hi @atoice ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @atoice ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[atoice]

Hi V-tsai,

I am unable to put in the table as it says too many characters and I cannot share in the cloud with a link due to company security. Unless you can do it with less data and simulate. I appreciate everything you have done to help.

[v-tsaipranay · Accepted solution]

Hi @atoice ,

Considering the current limitations in sharing the dataset due to character constraints and security policies, I would recommend raising a support ticket. They will be able to assist you more effectively.

To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

 How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

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

Thankyou.

[atoice]

Thank you V-tsai again for your help. I will shoot them something and see. But it has worked for what we need currently. Just figured I would try to manipulate dates / months / etc. to stop the break in the lines.

[v-tsaipranay]

Hi @atoice ,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[atoice]

Hi Solomon,

First thank you for your support. There is data for every week.

1. I did use your measure - and the gap is still there. I could not find the enable "Show items with no data."

2. I feel it is the calendar. The week ending (how data is pulled) has 2023 week numbers falling into the next month vs. 2024 and 2025. (ie week ending 4/1 for week 13 falls into April in 2023, while 2024 and 2025 fall in March).

3. I redropped the week number back in and it went back in order (even though the data still showed the gaps.

I have never created a control table. So, I will skip and try when I have more time.

I removed the Month for the X-axis and it is continuous. I will accept this layout.

Enjoy your weekend.

MT

[SolomonovAnton]

Based on what I see and what you described, here are the most likely causes and how to fix them:

---

🧩 Root Cause: Data Gaps or Calendar Mismatch

The breaks in your lines (especially when 2023 is added) are likely due to missing sales data for certain Calendar Week Order values or misalignment between your calendar table and fact table.

Here’s a breakdown of what could be happening:

---

✅ 1. Missing Week Combinations in Sales Data

If even one year is missing a value for a Calendar Week Order, Power BI won’t plot it unless you handle that explicitly.

Fix: Create a full cross join of Calendar × Year

Steps:

• Ensure your calendar table has all 52 weeks for every year (2023, 2024, 2025).

• Use a measure instead of a column for $ Sales Line to avoid gaps.

Here’s a safe DAX example:

Sales Line = 
CALCULATE(
    SUM('Sales'[SalesAmount]),
    REMOVEFILTERS('Calendar'[Month])  // Optional, if Month is used just for axis labels
)

Also, ensure you set the X-axis to “Show items with no data”:

• Click on your X-axis (Calendar Week Order)

• Go to "X-axis" settings in Visual Pane

• Enable "Show items with no data"

---

🔁 2. Calendar Table Might Be Off

Since you recreated the calendar, make sure these columns are in place and consistently formatted:

• Calendar Week Order (values 1-52)

• Date (continuous and complete)

• Year, Month, Week, etc.

• If you’re using ISO weeks, check for Week 53 and Week 1 edge cases (2023 might have 53 weeks, 2024 is a leap year, so date alignment can shift by a week).

---

🛠️ 3. Sort Order / Out-of-Order Axis

You mentioned "one week is out of order in March" — double-check the sort by:

• Go to Calendar Week Order

• Ensure it is sorted by a continuous numeric column, like Week Number, not by text (like Month)

---

✅ Optional Check: Add a Control Table

Create a simple Year × Week helper table (via Power Query or DAX) and join it to your Calendar. Helps ensure you’re plotting over a dense axis.

---

🔍 Debugging Tip:

Use a matrix visual with:

• Rows: Calendar Week Order

• Columns: Year

• Values: $ Sales Line

This will quickly show you where weeks are missing for a given year.