cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kirill_vaynberg Frequent Visitor
Frequent Visitor

Show all Dates on continuous X axis

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Show all Dates on continuous X axis

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.

 

q2.PNGq3.PNG

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Moderator v-qiuyu-msft
Moderator

Re: Show all Dates on continuous X axis

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.

 

q2.PNGq3.PNG

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kirill_vaynberg Frequent Visitor
Frequent Visitor

Re: Show all Dates on continuous X axis

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?

Moderator v-qiuyu-msft
Moderator

Re: Show all Dates on continuous X axis

Hi @kirill_vaynberg,

 

I'm not sure which criteria are used to determine the number of labels shown, but cased on my test, we can drag to increase chart width to make more date values display in X-axis.

 

You can submit an idea in Ideas forum.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
reymard Frequent Visitor
Frequent Visitor

Re: Show all Dates on continuous X axis

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.

davidcpbi Regular Visitor
Regular Visitor

Re: Show all Dates on continuous X axis

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.

Highlighted
Gauß452 Frequent Visitor
Frequent Visitor

Re: Show all Dates on continuous X axis

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).

 

 

jjlogan007 Frequent Visitor
Frequent Visitor

Re: Show all Dates on continuous X axis

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

davidcpbi Regular Visitor
Regular Visitor

Re: Show all Dates on continuous X axis

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.

ForrestC Frequent Visitor
Frequent Visitor

Re: Show all Dates on continuous X axis

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 62 members 1,291 guests
Please welcome our newest community members: