Date Table Sorting and Grouping

[negbc]

I have a standard date table and added columns to display Quarter, Quarter-Year, and then a Date Sort Column with the dax formula:

Date Sort Column =

'Date Table'[Year]*12 + 'Date Table'[Month]

However when I use the Quarter-Year column in a visual, it looks to separate the value for the months insteading of grouping the value for the quarter-year. 

Date sort column is removed but still multiple rows for "Q4"

It makes sense that when the date sort column is added to visual to have more than 1 row because the values are different. I'm not sure why Qtr-Year would cause this. Any insights? I need to use the column to show trending data across different years.

TIA

[GiuseppeMR · Accepted solution]

Is the date sort column used to define a custom sort of this visual?

If so, this implies that the granularity of the visual includes this dimension, even if the sort column is not visible.

Giuseppe

[parry2k]

@negbc is Qtr-year column sorted by another column? Can you remove that sorting and then check?

[negbc]

Yes, it is sorted by the Date Sort Column, if not the Qtr-Year column doesn't go chronologically as shown below

[GiuseppeMR · Accepted solution]

Is the date sort column used to define a custom sort of this visual?

If so, this implies that the granularity of the visual includes this dimension, even if the sort column is not visible.

Giuseppe

[negbc · Accepted solution]

Yes I use the date sort column to sort other columns such as Qtr-Year. With what you are saying, I need a calculated sort column that uses quarter instead of month. That way it will have the same granularity..

(OLD) Date Sort Column =

'Date Table'[Year]*12 + 'Date Table'[Month]

(NEW) Date Sort Column =

'Date Table'[Year]*12 + 'Date Table'[Qtr]

@GiuseppeMR Thank you!

[parry2k]

@negbc the % you are showing in the visual, is it a column or a measure?

[negbc]

A measure, the rest are calculated columns from the date table.

[miTutorials]

Can you attach pbix with dummy data to debug this ?