Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gmw
Regular Visitor

Sort by column not working - fiscal year months

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.

 

FY month number - data2.PNG

 

FY month number - result.PNGFY month number - sorted.PNG

 

Thanks!

1 ACCEPTED SOLUTION
gmw
Regular Visitor

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!

View solution in original post

10 REPLIES 10
gmw
Regular Visitor

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
Responsive Resident
Responsive Resident

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

 

 

@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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Microsoft Employee
Microsoft Employee

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])

2.PNG

 

Still under Query Editor mode, add a conditional column.

1.PNG

 

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

3.PNG

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Phil_Seamark
Microsoft Employee
Microsoft Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.