Dates missing on x axis of graph even though there is data displayed

[Anonymous]

Hello,

I have data that I want to show as a line graph. I made a test model with one single table that contains only two columns: Date and Value.

I have dates  for 13 previous months.

I have one value for each month.

In the general case there could be any number of values on any number of dates.

When I create a line chart visual it only shows every other month as a lable on the x-axis:

I have a sample PBIX file, but I'm not sure howto attach in here.

[amitchandak]

@Anonymous , This is because the axis type is continous. You can make it categorical

[Anonymous]

I tried that in my actual pbix, then it shows every single data of thousands of entries one for each data point. Where as before I had summary data by month.  Plus it gives me a horizantal scroll bar that goes on and on.  That's no good.

You can try the same thing in my test model uploaded to ufile.

See test model https://ufile.io/1zmguxim

What do I do to just have a graph that shows the data month by month, and doesn't skip month labels on the x axis?

[Anonymous]

So to explain myself a bit better, this is what it looks like as a continuous x axis date axis:

But when I set it as catagorical this is the result, which is not very useful:

See the horizontal scroll bar?

For a report that's destined to be output as a Power Point (image) file, that's not usable at all.

Is there something else I need to do to prepare my source data?  I was hoping not to have to do more data processing.

There must be something simple I'm missing.

[amitchandak]

@Anonymous , I told the solution to have month year and use that. I think one must have date table with all this

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[amitchandak · Accepted solution]

@Anonymous , You can create a month year in your table or date table(prefer)

Month Year = format([Date],"MMM-YYYY")

Month Year Sort =  format([Date],"YYYYMM")

Sort Month year or month year sort and use that in your visual

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

Hello Amitchandak,

So.. I really went around and around on this, then did some more training.  I initially had hope for built in date heirarchies, but then learned this isn't always the best way, especially from the folks at SQLBI, and yes I know, you said it above very briefly.

So no error on your part, its more how I learn, I really have to dig into things, I have an engineering mind.

So I constructed a YYYYMM column with this great post https://www.fourmoo.com/2015/03/25/power-query-adding-leading-zero-to-number-eg-month-number/ so I have values like 201501 in that column now. 

The other critical thing was something I saw in a video on you tube the sort by column command.

Honestly, I never would have thought, that if you have a full date table, antd you're linking a fact table to it, then showing the date table axis on the horizontal axis of a chart, that it wouldn't just automatically follow the date order in the date table for any corresponding dates. 

Clearly I have much to learn yet!

[Anonymous]

Hello Amitchandak,

Thank you for trying to help me with this problem.

Can you expand on your suggested solution? Do you have a sample pbix you could post so I could investigate?  I don't mean to come across as 'please hold my hand all the way' but I've only done about 20-30 hours training, and done about 50-100 hours development time so I'm not able to make use of your suggestion since I don't know if you mean:

Create a new column in my date table with the text you supplied as DAX?

Create a new column in the m query that creates my data table with the text you supplies as m-code?

Now I did go and try the following: I addeda date table to my test model.

I read the link you provided. Didn't seem to work for me (maybe new user silly mistake?)

Then I recalled the data modelling group bins command. I tried that and was able to get the test model to behave and look like this:

See new fileset here:

https://ufile.io/l6lcgnue

But if I try the date bins in my actual datafile that I can't share here, this is what happens; I loose the cumulative charting:

Learning this stuff is like one stop forward, and then another road block. Break through that one, take a step and ... another road block. ack.

[Anonymous]

I figured out why my running totals don't work when I change the x - axis in my last reply.

I did try a Month Year Sort as Amitchandak suggested, but we don't want the dates formatted as 201901 or 2019-01 etc.  We want to see: Jan 2019, Feb 2019, Mar 2019 etc. 

Referring again by to my second posted test model, https://ufile.io/l6lcgnue, there has to be a simple way to show a line chart with all the date lables by month.

Would love a second opinion on a solution if anyone has ideas?

[v-zhenbw-msft]

Hi @Anonymous ,

Do you want to show the value or cumulative value?

If you want to show the value, you need to create a month&year column, and put it in axis.

You can refer the following steps.

1. Create a dates column and a sort column in sheet1 table.

Dates = FORMAT(Sheet1[Date],"MMM") &" "& YEAR(Sheet1[Date])

sort = YEAR(Sheet1[Date])*100 + MONTH(Sheet1[Date])

2. Then put the Dates in axis and sort it by sort column.

If you have any question, please ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

Okay I wrote a long discussion reply of how I found a way to do this with info posted to https://whitepages.unlimitedviz.com/2020/05/formatting-the-x-axis-in-power-bi-charts-for-date-and-ti....  I included many screen captures etc but it seems to disappear.  I don't have time to repeat it.  Maybe my post is being moderated?

It was a good discussion, I'm miffed it's not shown.

Oh well maybe some other time.

[Anonymous]

Hello v-zhenbw-msft,

(Edited - extra blank lines removed)

Okay Thanks to this post I found I think I've figured it out. Though I *can't* figure out how to get the MMM-YY method to work, I think I've found a simpler method shown here based on a posting at https://whitepages.unlimitedviz.com/2020/05/formatting-the-x-axis-in-power-bi-charts-for-date-and-ti...

For the benefit of trying to clearly dump my brain and for the benefit of the community I'll walk through it here:

So I created a new source dataset covering two years with two data points per month like this:

Excel Soure Data for 2 years (not fully shown)

Next I related that table to my date table like so:

Date Table related to SetDataOver2Years

Then if I create a column chart with these settings I get the same problem of the missing dates on the x axis:

Default Column Chart

If I change the date (non heirarchy) x axis to categorical we get this result:

Categorical Date Axis

Not what I was looking for...

But following the guidance at the link I included earlier we change the date to Date Heirarchy:

(Shown after removing Quarter and Day options in the heirarchy)

Then my visual graph becomes:

Graph with Date Heirarchy. Top Level

Not too good still Right? But if I use the expand all levels down the heirarchy button:

Expand heirarchy button

Then I get this:

Expanded heirarchy graph

Now this is what I was looking for!

Now.. discussion points for those who really want to keep the propeller hat on tight:

• I'm not saying this is the best solution, I'm still learning.  But I think this approach will solve some current problems I'm facing.
• It seems I can publish the report with it in this expanded heirarchy state, and further I can turn of drill up and down buttons in the formatting options.

But Questions come to mind, when I try the MMM-YY method, recall my data is related like this:

Data Relationships (repeated)

Look at what happens to the data when I try to graph with MMM-YY as the X-Axis (I know I'm making some sill mistake here.. but I can't figure it out, and given the above discussion with my current deadline I'm not inclined to try to figure it out this week)

Notice the data is sorted in some way I can't understand.  I've zoomed in on some of the x-axis to show what I mean.

I know my solution is not the same as was proposed above where the dates were in the data table, but wanted to leverage what I thought was the power of a date table.  Is that wrong?

Anyway, if you're read this far, thanks for reading.

If you have a good way to build on this discussion for the benefit of other newbies in the community, that would be excellent.

Best Regards

- JT1024

[Anonymous]

Hello V-Zhenbw-msft,

Thanks for this.  I've been busy with some other urgent and important tasks. This solution looks promising. Thank-you very much for taking some of your time to look at this with me.  I'll try it out and report back how it works on my full 180,000 row dataset.  Hopefully later today.

- Jim