Forum Discussion
Need date sorting on bar graph
Create a new column in your table using the following DAX, then use the column to sort your Date column. For more details, please review this modified PBIX file.
Column = DATE(YEAR(OutageTrending_hour[cte_start_date]),MONTH(OutageTrending_hour[cte_start_date]),DAY(OutageTrending_hour[cte_start_date]))
Regards,Anonymous MattAllington
The key to sorting a *text* date column by another column is that the column used for sorting is set to Datetype: Date or Datetype: Whole number.
What the error is really saying is all like items have to have the same sort id.
PK_Date Month Month_Name Month_Period_Of_Time Month_Period_Of_Time_Reverse
2017-06-28 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-06-29 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-06-30 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-07-01 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250
2017-07-02 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250
2017-07-03 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250
We have a time table in sql.. we found this magic sql somewhere that sets the sort values correctly. For dates, you might have to strip the time off.
--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]
Anonymous MattAllington
The key to sorting a *text* date column by another column is that the column used for sorting is set to Datetype: Date or Datetype: Whole number.