Clustered bar chart X-axis with time

[jderekc]

Hi all,

I have a clustered bar chart where the X-axis is a time series and the Y-axis is a quantity value for product count comparisons between two systems.  I doubt that is important to any of you as the only thing I am having a problem with is the X-axis time series.  Basically, if I use a date slicer and restrict the date to a few days, I start getting the following:

October 10, 12:00PM

October 11, 12:00AM

October 11, 12:00PM

October 12, 12:00AM

Et cetera...

Is there a way to remove the 12-hour ticks from the clustered bar chart and have it display only the day?  This only happens when the chart has such a little amount of data that it seems that the visual tries to include half-day increments even though there's no time data really.  If anything, my time data is as follows: '2017-10-10 00:00:00.000'

My data never has time--to my knowledge--apart from the general zero hour.  Even if it did, I just want to show a whole date.

Any help is appreciated and let me know if I need to give you more information!

Regards,

Derek

[Greg_Deckler]

You should be able to just change the format of your column to Date instead of Date Time.

[jderekc]

Hi there, smoupre!  Thanks for responding.  My column data types are now "Date" but the issue persisted.  Previously the column data types were date/time, but I chose the M/dd/yyyy format.  Please see the included screenshots for what it looks like when I select only a few days versus what it looks like when I select more days:

Only a few days; get half-day ticksNo half-day ticks

[Greg_Deckler]

Hmm, that's odd. Well, you could create a new column and use FORMAT to ensure that it strips out the time information and then just use that column instead.

Alternatively, go into your query in Query Editor and switch the data type of your column there so that it comes into your data model without the time information.

[jderekc]

Tried both but got same results.  The initial time I used the FORMAT function in DAX, I thought I had it working but what happened was the X-axis was no longer categorized/continuous.  I would get data such as the following:

Oct 04: 900/1000

Oct 06: 100/300

Oct 07: 50/70

Oct 10: 125/160

I want the grid to be like so:

Oct 04: 900/1000

Oct 05: (blank)

Oct 06: 100/300

Oct 07: 50/70

Oct 08: (blank)

Oct 09: (blank)

Oct 10: 125/160

Ultimately changing data type in query editor or via direct modeling in the designer or using the DAX function, I was unable to get it to work.

[v-qiuyu-msft]

Hi @jderekc,

After you use FORMAT function in DAX to get a date column, you can create a calendar table(=CALENDAR("2017-1-1",TODAY())), change date column in calendar table as date data type. Then create a relationship between those two tables based on date column. Then you can drag the date column from calendar table to the X-axis of the chart. The X-axis value should be continuous now. 

Best Regards,
Qiuyun Yu 

[jderekc]

Hi @v-qiuyu-msft,

Thanks.  This works only if I use the date hierarchy and select "Day" from the drop-down.  If I keep the date continuous, doing this prevents the hour-ticks from showing.  If I don't do this and instead choose the date as a whole, I get the hour-ticks.  I created the calendar table as you suggested using dates from the beginning of 2017 until the end of 2027.  The column is formatted as a date column with no time associated with it.  I then established a relationship with the table that has the "posted date" column.  I checked and this column is also a date column with no time.  The relationship created successfully.

But, like I said, this only works with the "continuous" selection if you break out the date hierarchy.  It won't work if you keep the date "whole".

Using date hierarchy days after creating calendar tableUsing date as a whole after creating calendar table

[HansTheEnforcer]

Funny you used the word continuous.  If you click on the visualization and go to the Formatting section, and to the X-Axis dropdown, change the Type from Continuous to Categorical and the issue will go away.

[soccerjoshj07]

If you do this, you lose the ability to create a trend line 😕 

[sirlanceohlott]

@HansTheEnforcer THANK YOU, I was experiencing this issue and this trick with the X-Axis worked like a charm!

[MGPAnderson]

I have the same issue and what you are proposing is an awful lot of effect for one report, when it is a basic bug in Desktop.  I have several of these in my first reporting solution using Power BI and frankly, I am getting tired of having to code workarounds, when the visuals and the integration between Desktop and Dashboard should be better.  Please can Microsoft work on all of the bugs rather than giving us workarounds?  (I acknowledge that this ticket is 3 days old but really, this must be a long standing bug.)  It's such a shame as so much effort has gone into the data engines that the visuals but this and the other smaller issues that I have found, in just one week of using Power BI, just let it all down.  This issue is indicative of all bar charts.