Sort by column not working - fiscal year months

[gmw]

Hi all,

I've read through various versions of this but no solutions have worked. Basically, I'm trying to make a bar chart start with September per our fiscal year. I made a FY month number column and sorted the date (labeled Parse, ignore my redundant data) by this column in the Modelling tab. End result is does not follow this sort, or a normal calendar year sort, but is instead seemingly arbitrary. What am I doing wrong?

Other possibly important details: FY month number is stored as whole number, date is stored as a date.

Thanks!

[gmw · Accepted solution]

OK folks, if anyone else with my minimal DAX skills is looking to do this, I managed to solve it myself in a probably sort of ridiculous way.

The DAX solutions offered just were not working for me, so what I did was make groups of all the months in my original data (i.e. a September group with 2 pieces of data for FY16 and FY17, an October group, and so on) and then created a separate table with just the group and a number for the order I wanted them to be in:

Group / Order

September / 1

October / 2

etc

I imported this table and created a relationship with the table which had all of my other data. I then added the Order to the tooltip section of the viz and told it to sort that way. I'm sure there are more technologically savvy ways to do this, but they just were not working and this did, so... I'll take it!

[Yggdrasill]

You can't use one sort value for 2 or more values to sort. For example, you can't sort December 2017 and December 2018 by the same value. 

Create a YearMonth KEY

Try this in DAX and create a calculated column

SortYearMonth = YEAR(yourdatecolumn) * 100 + (yourfiscalmonthcolumn)

That way you should end up with a number like: 

201701

201702

...

201811

201812

[gmw]

@Yggdrasill - ok, sorry for all the questions, will this allow me to compare month to month, or would this then put them all in order starting with Sept 2016?

[Yggdrasill]

For that create a measure

M1 = sum(yournumbercolumn)
M2 = calculate(M1 ; sameperiodlastyear( dimdate[datecolumn] ) )

put those 2 measures in a chart and they columns will appear side by side so you can compare visually months in current year vs last year

[Yggdrasill]

You can't use one sort value for 2 or more values to sort. For example, you can't sort December 2017 and December 2018 by the same value. 

Create a YearMonth KEY

Try this in DAX and create a calculated column

SortYearMonth = YEAR(yourdatecolumn) * 100 + (yourfiscalmonthcolumn)

That way you should end up with a number like: 

201701

201702

...

201811

201812

[v-yulgu-msft]

Hi @gmw,

How did you create the [FY month number] column? You should create it as a conditional column in Query Editor mode.

Regards,

Yuliana Gu

[gmw]

Hi there! Tried this but I'm not sure I did it correctly because it did not work. Would it be conditional based on my column pictured above, Parse  - i.e. if the date is 9/1/16 then the new column has a 1? This is what I tried but perhaps not what you meant.

Prior to this I had made the column myself since the data is so small and it was quicker. I'm curious why that would make a difference - mind explaining?

[v-yulgu-msft]

Hi @gmw,

You only need t sort the Month in specific order, right? That is to say, on X-axis, it shows Month from September to Auguest without adding Year, right?

If so, please see below steps:

In Query Editor mode, add a MonthNo:

MonthNo=Date.Month([Date])

Still under Query Editor mode, add a conditional column.

Save all above changes, return back to report view. Then, add [Month] to X-Axis, add Year number to legend.

Regards,

Yuliana Gu

[Phil_Seamark]

Hi @gmw

Have you check the sort order for the visual?  You can secifiy that it is sorted by the Axis column or by the value.

This should be in the top right hand corner of the visual from the small drop down arrow.

[gmw]

Weirdly, using this puts the months in a new but still incorrect/indecipherable.