Show all Dates on continuous X axis

[kirill_vaynberg]

I would like to have a line chart that plots a numerical value against date. Everything seems to work except for the Date axis: it's always stuck at showing Month/Year at best. I would like to axis to actually show individual dates.

I have tried enabling Hierarchy but that is still stuck at Month/Year regardless of what type and level of drill down I am using. Besides, I have no need to the hierarchy features.

I would really rather not use a categorical axis as I need the results to show in the correct chronological proportion (observations are not taken on a regular basis)

The plot itself is fine, the Date Type & Format for the Date column is set to Date and (MM/dd/yyyy).

Ideally, I would like to just enter the step for that axis manually as per older versions of Excel

EDIT: Spelling

[brandon-sigao]

Posting just too keep this thread going because 7 YEARS later and the issue is still alive and well.

Here are some interesting facts:
-This issue the age of my company.

-Microsoft has known about this issue for roughly 22% of my lifetime.

-If this issue were a child, it would be in school.

-On average, all of the cells in your body are replaced every 7 years, which means 36 trillion cells can replicate and arrange themselves into one of the most complex pieces of biological machinary on the planet in less time than it takes Microsoft to give you the option to show all the months on an x axis.
 

[Smatralph]

Nothing I've seen today is more hilarious. 😂

[MustafaTorun]

You can first shift to categorical view of X-axis and from visual sort the axis accordingly. This is the ultimate solution. 

[Jahangir]

Amazing, thanks.

[nadeemabbasi]

All though the thread is very old but I have similar case case where I need to show all date type labels for x-axis. The provide solution was not fullfilling requriment. The best way to achive this job create a custom column using Format funtion where you can achive any type of Date format and it will show all labels on X-axis.

Format(MyTableName[Date],"mmm-yy")

[frankvb]

In the end I does anybody have a solution in the end for:

-line chart

-trend line

-display all values on x-axis

-missing values in the line chart not connected by line

?

[EktaSoni_R]

I am also looking for the same , want date format as mm/dd/yyyy but it is displaying as mmm-yyyy,

- Line chart --> x axis --> continous 

- Trend Line is required so can't change to the Categorical

[Anonymous]

Added a date column using start of month and things worked out.

Date = STARTOFMONTH([YOURDATEFIELD])

[Anonymous]

I really don't think this is a solution....we need PowerBI to allow us to have a trend line using a categorical axis, or show all the months on a linear axis.  No matter how large I make the chart area it only shows every second month.

[dlew]

I know this is an old post but came across the same issue. Maybe it's been properly fixed by now but I wanted a trend line on a column chart with categorical axis to show labels for each column.
I did find a work around which gives me everything I wanted but it's FAR from ideal.
I set my chart to continuous to get the trend line and removed the x-axis labels and title. I then created a  duplicate of the chart all set to white other than the x-axis labels and title and changed this one to categorical - lined it up with my other chart and chucked it to the back so only the labels and title were visible on the x-axis. et voila - a continuous column chart with trend line and x-axis labels for each column

[brandon-sigao]

I just wanted to point out that this is STILL an issue in October of 2024, and the fact that Microsoft hasn't fixed this embarassing. I'm in disbelief. I cannot show "December" on a 1 year chart because it won't do anything but every other month, despite having all the space in the world.

Absolutely shameful to let something like this go so long.

[jjlogan007]

I had same requirement: continuous dates on X-axis and event counts on Y-axis (0 to ....).  to solve this I created a simple Calendar table with Dates in one column continuous from my start date to last date in my period under review - did this in Excel but of course can use other data source (Access, etc). Save or close the Excel before import. I am using PowerBI Desktop. Then Get Data, ensure Date format. Then Load query. Then link Calendar table to fact table in Relationships. Then in visual use Date from fact table and format x-axis as Continuous / Linear. Hope this helps. JL

[ForrestC]

I'd also like to add 3 things that are pretty obvious (though I missed them).  These are small, but critical to ensure that this works:

1. Make sure your custom date table is set as the default date table.  PowerBI defaults to its own date table until you define your own date table.  Right click on your date table and choose "Mark As Date Table".  https://docs.microsoft.com/en-us/power-bi/desktop-date-tables   (Side note: you can also build your own Hierarchy from your date table Year > Quarter > Month > Week > Date)

2. Make sure the visual's X-axis is set to "Show Items With No Data" to make sure that days with no value are still included in the visual.  Right click on the Axis field in the Fields list to do this.

3.  Make sure your X-axis is set to Continuous.  Click on your visual, then go to Format and expand the X-axis options.

[v-qiuyu-msft · Accepted solution]

Hi @kirill_vaynberg,

When you drag the date column to Axis of a line chart, it will automatically enable date hierarchy. You can check the column name instead of the Date Hierarchy. The date value in the X-axis will display based on the visual width and number of records in this date column. We are not able to set interval for date in the X-axis, we can just specify the start date and end date.

To display all date values in x-axis, the best option is to set X-axis type as Categorical, it will generate a horizontal scroll bar for us to view all data if there are two many records.

Best Regards,
Qiuyun Yu

[Anonymous]

I have the same need except I need date & time to be considered as one value.  For example the chart may extend from 8:00PM on 7/14/2019 until 8:00PM on 7/15/2019 and the tick marks will likely be every couple of hours.  If the user selects a wider range, the tick marks may become days.  The point being I need Power BI to treat date-time as one field.  How is this done in Power BI? @v-qiuyu-msft 

[reymard]

Hi @v-qiuyu-msft

If I use a Categorical for my X-axis, then I'm not able to display a trend line using the Analytics pane. Is there any other way to keep the Continuous type and to display all the months names ?

Thank you for your help.

[Anonymous]

This is my exact requirements any solution?

[Anonymous]

I had the exact same need today....I want to show a trend line but cannot if I use Categorical.  Please allow us to show a monthly interval with a trendline.

[Gauß452]

Does anybody have a solution for this problem?
If I want to see a trend or the whole year in a chart (without scrolling), there are gaps between the weeks (weekend  = gap).

[kirill_vaynberg]

Hi @v-qiuyu-msft

Thank you very much for the clarification. Are you able to share what criteria are uded to determine the number of labels shown? If I know the algorithm that uses visual width and number of records in this date column, I could try massaging my data and layout to match it in the future.

What pathways are there if i would like to propose setting interval for date in the X-axis as a future option?