Displaying x-axis of a line chart using month labels, but day granularity

[Anonymous]

Hi gurus. Having tried looking everywhere, I cannot find a solutions to this.

I have a chart like this which shows cumulative or running totals split by year.

How can I change the x-axis to show month labels instead of day of the year whilst still maintining the daily granularity??

I have a calendar table linked by date to the the data table.
Chart details:

Axis:
Day of Year

Legend:
Year

Values:

Cumulative

[parry2k]

@Anonymous Hi there, sorry, didn't have a chance to look at this. May be will try it soon, although I feel there is very little chance that it will work. The reason is you cannot control the format on the x-axis if you are using continuous, so the new date column we added, will give a different format (whatever format works internally) and might not look as expected, having said that I will surely give it a try, stay tuned.

[parry2k]

@Anonymous

Thanks for your feedback on the video. Much appreciated.

Do you have relationship between your transaction table and calendar table? Can you share the relationship diagram picture? Or maybe it will be easy if you can share pbix file, (remove sensitive information before sharing). Share using one drive/google drive.

[Anonymous]

Hi again @parry2k 

It's becoming clear to me that I don't think that this is possible. In Excel, you can select a range to name your axis labels. It does not seem like you can customise in the same way in PBI. Yes, you can have your line graph display an x axis of Day Of The Year, that will give you daily granularity. Sure you can use month labels instead but this also changes the granularity to monthly and you cannot retain daily granularity and see all the daily data points in your graph. Have you ever created this or seen this being created? I have searched and searched and cannot find any example where this is possible for a graph like i showed in my original post.

[v-easonf-msft]

Hi, @Anonymous 

PowerBI does have limitations in this regard. Even you can retain daily granularity, the final result may not meet your expectations.

For your requirement , you can submit an idea on Power BI Ideas and add your comments there.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @parry2k thanks again for your help with this.
Here is a link to the pbix here

[Anonymous]

Hi @parry2k 

Luckily it's very simple in the data model view. The date field in data table has a relationship to that in the calendar table:

If that makes no sense I'll share the PBIX file, just might be tomorrow so I can remove sensitive data.

[parry2k]

@Anonymous you need to watch this video on my YouTube channel to understand all this https://youtu.be/BKzsCfr4qeQ

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thanks @parry2k so much for your time so far and a fantastic video about this. I have disabled in the settings for this report any hidden auto calendars from being created. I have also Marked as DateTable the DateTable using the button in the table tools tab. But this now leaves me with the blank graph below:

[parry2k]

@Anonymous why do you have a hierarchy, remove the hierarchy. That's the problem. Click on the arrow next to date for x-axis and select Date for x-axis instead of Date Hiearchy

[parry2k]

@Anonymous ok try to change the format to mmm, dd

[Anonymous]

@parry2k ok i changed it to 'mmm-dd'
Gives me Jan-01, Jan-02, Jan-03 etc...

Now when I go to the reporting canvas after refresh, the chart looks like this:

In the Fields pane in DateTable, Date for x-axis column is a date hierarchy.

But as you can see above, although the chart has long month name labels, it has lost its daily granularity and value points are only in monthly increments. So I still face the same issue as I the labels are fine but the granularity has shifted from daily to monthly.

[parry2k]

@Anonymous my bad, the year value needs to be above 1900, so maybe put 1901 and that will do it. 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k Yes!
I was just about to write to you to day the same! Everything from 1901 seems to work.
After I enter 'mmm' in the Format dropdown, it's giving me the first three letters of the related month.
So I think the columns is populating as it should.
But then I have the task of linking it to said graph. I'm using:
Axis:

Date for x-axis

   - month

Legend:
Year

Values:
Cumulative BT

I'm now getting this. However, I've now lost the daily granularity that was displayed previously and now it jumps by month. Can I retain the monthly labels but the line updates daily as before?

[parry2k]

@Anonymous weird, maybe change the year to 1950 or something like that, if you just put "DATE (" , what does it shows? What values it expect?

[parry2k]

@Anonymous you have to change the name of datetable to the name of the table in your model, if it is called "Calendar" then change it to "calendar".

[Anonymous]

Hi @parry2k 
Yes I actually did this but still the same error. The table is called DateTable and the column with the date, i.e. 21/11/2020 is named 'Date'

[parry2k]

@Anonymous yes create DAX custom column in your date/calendar table.

For custom format string, select the new column you create and just manually enter the custom format string mmm at the image below

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k 

I'm getting the following error when trying to generate the column by DAX:

Any ideas?

[parry2k]

@Anonymous try this, not sure but add a column for x-axis

Date for x-axis = 
DATE ( 1900, MONTH ( DATETABLE[Date] ), DAY ( DATETABLE[Date] ) )

and change the format of this column to "mmm" by adding the custom format string

and now use it on x-axis and it should work.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k thanks for coming back to me on it.

You might have to spell this out for me.

Do you mean create a DAX custom column?
I think I'm right in saying I add this to me Calendar table.
How do I customise the format? I go to Column Tools > Format but I can't see a selcction in the dropdown there to customise a date format to mmm.