Chart sort order per month

[PowerBiNoob]

Hi,

I am busy with a couple of charts , the info need to be displayed needs to be per month , but a different kind of sort order is needed. currently there is (Jan,Feb,Mar..) or (Mar,Feb,Jan) , I need to get it sorted where the History months is at the back(Left) and the new months goint forward on the right (Nov,Dec,Jan,Feb,Mar) , I got it to work by adding a ID field as well as the month date in the axis and displaying both (1 Nov,2 Dec, 3 Jan, 4 Feb,5 Mar....) , but I dont want to display the ID part as it is confusing. I tried to do the default sort order just on the ID and just using the month part on the axis but that doesn't seem to work as what I planned.

Any help or guidance will be appreciated.

Regards

[synergised]

We have a requirement to sort everything in reverse order.  We are using a Time table that has Day, Month, Year so we just added the sort month column there.  First attempt "Sort by Another Column Error" .. you can't have more than one value for the same value...etc.  Finally found this bit of magic sql - sorry we couldn't find the link back to the original source.

Basically every month value has to have the same sort value.. 1/1/2017 = 1, 2/1/2017 = 2, etc..

PK_Date                                Month                                 Month_Name    Week    Week_Name    Month_Period_Of_Time    Month_Period_Of_Time_Reverse
2017-01-01 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2016-12-26 00:00:00.000    Week 1, 2017    1462    1431
2017-01-02 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-02 00:00:00.000    Week 2, 2017    1462    1431
2017-01-03 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-02 00:00:00.000    Week 2, 2017    1462    1431
2017-01-04 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-02 00:00:00.000    Week 2, 2017    1462    1431

--Month
UPDATE dbo.Time SET Month_Period_Of_Time = RowNumber
FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY  [Month]) AS RowNumber FROM dbo.Time) AS TMR
WHERE TM.[Month] = TMR.[Month]

UPDATE dbo.Time SET Month_Period_Of_Time_Reverse = RowNumber
FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY  [Month] DESC) AS RowNumber FROM dbo.Time) AS TMR
WHERE TM.[Month] = TMR.[Month]

This also helps with the scenario where you want just the Month names.. but still want them sorted in the correct order.

[v-sihou-msft]

@PowerBiNoob

In this scenario, if you want to sort the months based on current month (current month on top). You could add a month sort column like below:

month sort = IF(MONTH('Table'[Date])-MONTH(NOW())<=0,MONTH(NOW())-MONTH('Table'[Date])+1,MONTH(NOW())-MONTH('Table'[Date])+13)

Then you can just sort the months based on above column.

Regards,

[dedelman_clng]

Sounds like you're doing a rolling 12 months?

In that case what I've used in the past is a calendar table that is filtered to only show the rolling 12 month period.  You can't do this natively from the PowerBI GUI, but if you go into "Advanced Editor" and add something similar to the following lines of code, your model will have only the last 365 days worth of data.  Then you display the date as the X-axis and hide the year and it should sort how you want it.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow())),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each [Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -1))

(#"Changed Type" should be renamed to whatever the last Applied Step is in your query.  Also adjust #"Filtered Rows" if you've done other filtering beforehand)

I would love to hear other recommendations on how to do Rolling 12 month visualizations that refresh on their own (i.e., without having to change the page/visual filter manually each time you refresh the data)

[Phil_Seamark · Accepted solution]

Try adding this column to your Date table and use the Sort By Column on your real month column to use this one.

Month Sort Col = 0 - INT(FORMAT('Dates'[Date],"YYYYMM"))