Cannot change date format on Pivot Chart created using Power Pivot

[JollyRoger01]

I have been through hundreds of web pages looking for a solution to this and have not been able to find a solution. Apparently, if creating a Pivot Table and subsequent Pivot Chart from the Data Model, I need to ensure that there are no 0's or blanks in the date column if I want to access the Number Format button when left clicking on the date field in the Field Settings. I have gone through my data over and over and everything is fine, I have no missing values. I can change the format of the pivot table fine using the normal number format dropdown from the ribbon. But the axis of the chart refuses to change. I have tried changing the format in Power Pivot, through the Format Axis bit, using the dropdown on the ribbon... Nothing works!

Could someone please help me and change the number format in the workbook below to DD MMM YYYY?

https://jmservicescomau-my.sharepoi...FHjzI-NXv75ssBYhap6vPwpRCut2qnTUdTRg?e=xXRxM9

 

[Alex Blakenburg]

Can you reshare the workbook allowing anyone with the link to open it. I suspect you can't do that using Sharepoint so if you can try Google drive, OneDrive, Dropbox or one of the other sharing platforms.

Some considerations:
Did you load the data into the data model ? If so what is the columns Data Type set to ?
If you have a table what do you get if you filter the offending column for blanks or text or Errors such as #N/A ?
Also with no filters applied to the Pivot, go to the date filter drop down on the pivot and look for the items that appear right at the beggining of the list and right at the end of the list.
Dates out of order will be text and blanks and #errors will also float to the top or bottom.

 

[Matt Allington]

Try removing all date columns from all pivots, make sure the format of the date column is as needed in the model. Go to the model and add a calc column (column:=1 is fine). This forces a change to the model and should refresh the cache. For good measure, go back to excel and refresh all. Go to a new page and insert a new Pivot.
if that doesn’t work, then

 

[rhettgoodman]

Add Column in your Power Pivot Table. Use FORMAT('Date'[Date],"DD/MMM/YYYY")
Use this new field for your pivot table X axis.
This has been the only solution that I've found.