Sort Area Chart by date like "01 Jan"

Hi! :slight_smile:

I’ve got a problem with sorting my area chart. Problem appiears when i choose 2 months in a slicer. When 1 month is selected we can see cumulative value like below:

When I choose 2 months it looks like this:

PBI sorts it like 01 Jan, 01 Feb, 02 Jan, 02 Feb etc.

Below is my calendar, i’ve tried sorting column with this data by Day&Month and Day%Month w/SPACE. Result is the same.

Is there a posibility to show first one month and then second month?

Hello @wojteq,

Thank You for posting your query onto the Forum.

Well first things first, as you mentioned that - “I’ve tried sorting column with this data by Day&Month and Day%Month w/SPACE. Result is the same.

The result that you’re not getting is just because what I can see from the above screenshot is that all the values are either in the “Text” format or are converted into the “Text” format. Try changing the format of the column “Day&Month” column and then try to sort again.

If this doesn’t helps then please provide the working of the PBIX file so that members of our forum can assist you and better results in an efficient manner.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,

I’ve format that column to whole number but result is still the same.

The problem is that i can’t publish my PBIX because there is a loooooot of companies data :frowning:

Maybe i should create some new column and sort my Day&MonthName column by it?

Hello @wojteq,

Well rather than creating additional columns in order to sort the visualization and going through that hardships you can simply use the “Date” table which was created by one of our expert member @Melissa. It contains all the features that one ideal date table should have. You can copy the code which is available under the “M Code Showcase” and paste it in your “Advanced Editor” and invoke the “Date” table. I’m providing a link from where you can copy the code.

I’m also providing a link of a video where it shows “How to Hide the Sensitive Data” which was created by one of our expert member @BrianJ.

Because without looking at the data it will involve lot of guess work for our members in order to provide the solution.

Thanks and Warm Regards,


This additional video might help – it’s one I did recently on a generalizable approach for sorting even the most recalcitrant date columns.

  • Brian

Hi @wojteq,

Welcome to the community!

To use “Sort by” in the Date table you’d need an Interger (Day&Month) at the same granularity as the Attribute (Day&MonthName) you want to Sort…

Looking at the Interger you’ve created I would suggest changing the logic as follows:
(Monthnumer * 100) + Daynumber

Then “Sort by” will get you the desired result as long as you don’t cross over year boundaries…

1 Like

Thanks for trying to help. My calendar is actually @Melissa calenar :slight_smile: It’s great!

Thx, I’ll watch this video at home :slight_smile:

I make something like this:


But the result is like below. It’s different then before, but still not good enough :frowning:
paź is october and wrz is september :slight_smile:

It works with 1-number months, 501, 601 etc. But with October (1002 etc) it does not.

Oh, it works! I just forgot to change type to Whole Number! Thanks @Melissa !!!

1 Like


Okay, day and month are both a bit troublesome because they both need to be padded with a zero to keep the sort in proper order . Here’s what I would do - using the technique from the third example in the video I sent you create the following sort column:

  • Start with a leading 1 for all values - this will keep the zero padding for single digit days and months from disappearing
  • then concatenate a two digit padded month
  • finally concatenate a two digit padded day

So, for example, May 1 will be 10501 and October 2 will be 11002. This should sort exactly the way you want it to.

Give a shout if you have any problem constructing the sort column per above.

  • Brian
1 Like

Thanks @BrianJ but it works fine with @Melissa solution :slight_smile: But i will watch your video and learn some more stuff of course :slight_smile:


@Melissa’s solutions are always on point. I just looked at your last post and saw you were still having problems, so posted mine before I saw your post about changing to whole number. All good.

FYI - for future ref, to make my suggested approach more durable over time, I would start with the four-digit year, rather than the “1”.

  • Brian

Agree with you @BrianJ

But for future reference the result will be equal to the DayInt column already present in the extended date table and the granularity <> Day&MonthName making it useless as a “Sort by” column for that specific Attribute.


Ack. You’re absolutely right - thanks!

I’m clearly undercaffeinated this morning. @wojteq - please ignore my last post…

  • Brian