How to apply mmm date format to datamodel pivot chart axis?
Our take
I'm using pivot charts to display data by month. All of my data is loaded into the data model with powerquery. I need to show the date as mmm, but also preserving the chronological sort order. So I have a table with a record for each month with the first day of the month as a date value.
I don't have any grouping on this field or in any other tables. The data model doesn't have an option for the mmm format, and doesn't seem to do anything to the pivotables anyway. I can't find an option to set a number format for the field in the field settings. Defining the number format for the column of cells on the pivot table doesn't change the pivot chart. I tried to change the number format of the chart axis, but cant get it to apply.
Somehow I was able to do this with one pivot table and chart, but I couldn't replicate it. Removing the date from this pivot table and re-adding it also removes the mmm format.
Is there a known proper way to do this?
I was eventually able to set the number format in a table and chart with vba.
ActiveSheet.PivotTables("PivotTable1").CubeFields("[fPeriodRef].[Day1]").PivotFields(1).NumberFormat="mmm"
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Unable to Group by Month on Pivot Tables.I have a weird situation that I can't find a solution to online. If I export my Power Query* query into a table then make a pivot table out of that, the option to group by months automatically comes out. But if I export my query directly into pivot table, Excel won't let me group my dates into months - group field, group selection, all those don't work. Any idea why this is happening? It can't be the format cause query>table>pivot table works. The second pivot table is loaded into the data model.* Could this be the reason why? *edited Edit2: using DATEVALUE() on a Pivot Table date gives a #VALUE error. Is there any info how Pivot Table formats dates? Wrapping the date in a TEXT(,"m-dd-yyyy") fixes it. The left indent on the right PT could be a clue but I don't know how to interpret that. submitted by /u/monxstar [link] [comments]
- problem with pivot table date columnsi had full dates, then i made them only mm/yyy type, but when put in the pivot table they still count the same month as different columns because they still count the days in, even though i changed it to mmm/yyy https://preview.redd.it/jds1lkbg9xtg1.png?width=1088&format=png&auto=webp&s=869a4bf431c54e6e9603d3cb25fe2bc53981b64b https://preview.redd.it/l8px4ijk9xtg1.png?width=164&format=png&auto=webp&s=01a8e47a9b24ba184aa097af2c212e453ec1c4b2 thats the view from the forula tab, idk why they still count the days, how do i solve this? https://preview.redd.it/wtsz4wrl9xtg1.png?width=283&format=png&auto=webp&s=5cd03a6a328ee992de70b209e429441f11294188 submitted by /u/PurpleDurian7220 [link] [comments]
- how to actually change the date and not just its appearancei am tring to do a pivot table with month-year formatting, so i went ot custom and wrote 'mmm-year', visually all the cells looks good, but in the formula tab the days are still there, i tied copy pasting only values and still no luck https://preview.redd.it/yqjhwuawsrtg1.png?width=164&format=png&auto=webp&s=134a10680cd9428dbadeaff051f4f608c30f82dc and tis is what is shown in the formula, it is bad becuase i need all the dates in a pivot table but days mess this stuff, 10/jan/2026 is diffrent than 30 jan/2026 and i want them to be one https://preview.redd.it/emvpgvawsrtg1.png?width=283&format=png&auto=webp&s=1042d4e752bca6735e1e1daf8b2d422af722998d submitted by /u/PurpleDurian7220 [link] [comments]
- How can I create a pivot table with the PowerPivot Measure being used for the rows?Building an inventory valuation model. Basically, taking data from sales, warehouse inventory, and stores to find lower of costs/sales value. I'm also applying a obsolescence formula that basically just says "if we sold 12.5% of our all available units over the year, then mark it as current, otherwise mark it as the last date we received it in the warehouse". This measure is named "Slow moving date". I'd like to use "slow moving date" as a row in a pivot table, but Excel says that value can't be a row. How can I force it to allow the dates as a row without having to copy/paste numbers only from a different pivot to make it work? submitted by /u/scaredycat_z [link] [comments]