Change formatting of date on chart axis

[Anonymous]

I have some sales data over time that I'm trying to plot in a chart. I want the column chart to show time along the x-axis and sales amount on the y-axis. I also want to show all time periods regardless of whether there were any sales in that particular period. 

Sales are recorded by period which I've approximated to date - e.g. Period 8 2016 is recorded as 1/8/16 - it's a fudge but it allows me to show items with no data. The problem is that my chart is showing the full date e.g. Aug 2016 whereas I want it to show 8-2016 as Period 8 is not August.

I used

        Period = FORMAT(Sheet2[Fisc Yr/Pd], "m-YYYY") 

To get my date in the right format, but the new column is text which means if I use it as the x-axis I get the right format but lose the items with no data and the sort order is wrong, and if I change it to date then it reverts back to the original format (Jan 2016). None of the suggested date formats fit what I need.

So, is there a way I can change the format of the date on the x-axis?

[v-yuta-msft]

Hi otter77,

To achieve your requirement, you can create a calculate column using DAX formula as below:

Date Modified = DATE(YEAR(Table1[Date]), DAY(Table1[Date]), MONTH(Table1[Date]))

 

Regards,

Jimmy Tao

[Anonymous]

Thanks @v-yuta-msft - that works as you say but isn't quite what I need as I just want month and year, I don't want the day in at all and the date function won't work with just YEAR and MONTH.

I've done a workaround where I keep the periods as text and can now show items with no data, but I really want to be able to have it as a time series so I can use sliders for the period selection - if I end up not being able to do this because I can't display the date in the format I want then that's really frustrating, especially as excel has a custom date format that does exactly what I need. 

[v-yuta-msft]

Hi otter77,

To remove day in new column, click new column-> Modeling-> Format-> select "2001.3(yyyy.M)", the result is as below:

 

Regards,

Jimmy Tao

[Anonymous]

I don't have that as an option @v-yuta-msft, I only see:

[v-yuta-msft]

Hi otter77,

Which power bi desktop version are you using? Please check if you have updated to the latest version. Also you can download the English version and try again.

In addtion, another way to create such a new column is that you can create a custom column in Query Editor using M code like below:

Date Modified = each Date.FromText(Number.ToText(Date.Year([Date])) & "/" & Number.ToText(Date.Day([Date]))))

Regards,

Jimmy Tao

[Anonymous · Accepted solution]

Thanks @v-yuta-msft for your continued assistance.

I have the June 2018 version of PowerBI (Version: 2.59.5135.421 64-bit (June 2018)) which I think is the most updated version. I have to go through our IT department to get them to update or reinstall the software so am loathe to do that unless it is absolutely necessary, I checked on a different computer which had PowerBI installed after me and I see the same date format options on that machine too, so I don't think it can be a version control issue, although it doesn't make sense that different formats are available to different people?

I did make an attempt at the Query Editor but kept getting error messages and I don't know enough at the M syntax to work my way out of it - will go and do some reading though.

I also found this: https://community.powerbi.com/t5/Desktop/Converting-from-Text-to-Date/td-p/133251 which is proving helpful and may be of use to others.

Mostly I'm bemused as to why such a simple task (display date as m-YYYY on a chart axis) has involved nearly two days of trying workarounds suggested by the very helpful people in this forum - I'll definitely think twice about doing anything date related in PowerBI again!

[tperry8715]

When I create a progress line chart the dates don't show as a normal date format.  How do I modify to show the actual date in the Y Axis?

[jthomson]

Surely this is more easily solved by using the sort by column feature? Maybe making a separate table with a list of your periods and the sort order (either an index column or something that can extract, say, 201608 from Period 8 2016) then relate it if needed?

[Anonymous]

Hi @jthomson - I tried that and that does indeed get them in the right order but I then lose the periods with no data again (even with "show items with no data" ticked) because its text not a date.