Forum Discussion
Chart sort order per month
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"))
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.