I’ve had very often a requirement were I had to put a date in a format Mmm yyyy into a header of a crosstab. Creating just a calculation =[Month Name]+” “+[Year] will automatically sort an output in an alphabetical order.
Most of the time, if not always, the requirement will be to have a proper date order.
The first step would be to display a date in a crosstab header in a format MM/dd/yyyy. Make sure that you show only one day of the month in dd format, I choose first day of the month, this will guarantee that you will have only 1 month in each year displayed. In my case I use eFashion data so I need to create a formula like this:
If you have a full date you would need to truncate it to the first day of each month.
Now we have a proper sort of data, just the date format needs to be changed.
The second step we can do it in few ways:
Solution 1 – Apply a custom date format
Solution 2 – Apply an alerter with a proper date format. In an alerter condition you can put any condition that will always be true, I put =1=1. In the Display box put a proper format:
=FormatDate( ToDate([Month]+”/01/”+[Year];”MM/dd/yyyy”); “Mmmm yyyy”)
Solution 3 – Create an additional header row below and enter a date as follows:
=[Month Name]+” “+[Year]
The second header row would have a proper format:
Now we just need to make the first row white and decrease its height. This way the first row will enforce the right sort.
Personally I always use the first option because it’s easy and quick.