I have a star schema model. One of my dimension tables is a date table. It was built outside of Power BI since it has columns for our fiscal calendar, which is unusual (4-5-4, 52/53 calendar) and doesn't start on January 1. (That is, you would never want to generate this calendar within Power BI.) Its grain is day, and 3 of its columns include [Date] (PK) (e.g., '2019-11-9'), [Fiscal Year - Period Name] (e.g., '2019-2020 - November', which is period 8 in our calendar), and [Fiscal Year - Period Number] (a whole number, e.g., 201920208). My fact table has date keys (at the grain of day) that span November 2019 up through May 2020. The fact and date dimension tables join on the [Date] key.
I want to create a line chart of some measure by a drillable hierarchy that consists of [Fiscal Year - Period Name] \ [Date]. So, I have created the desired field hierarchy (in the 'Fields' pane). By default, [Fiscal Year - Period Name] will sort alphabetically (since it's a string), which is not the sorting that I want (obviously). I want it to sort chronologically. So, I have sorted my date dimension table by a corresponding numeric column, i.e., [Fiscal Year - Period Number]. (It can't be sorted by [Date], since there are many [Date] values per [Fiscal Year - Period Name] value.)
When I create a line chart with axis of just [Fiscal Year - Period Name] (not the hierarchy), the sorting in the line chart is chronological, which is expected (since I sorted the date dimension table). When I create a line chart with axis of just [Date] (not the hierarchy), the sorting in the line chart is chronological, which is expected (not because I sorted the date dimension table, but because [Date] is a date data type).
But, when I create a line chart with axis of the hierarchy I have defined, it is not sorting as expected. It incorrectly sorts the line chart alphabetically at the level of [Fiscal Year - Period Name], as if it's ignoring my date dimension table sorting that should have resolved this sorting problem. Mind you, when I drill down to the level of [Date], the sort is chronological, which is desired.
How can I resolve this sorting problem with my hierarchy?
