date hierarchy sorting

[Anonymous]

I have a simple star schema model.  One of my dimension tables is a date table.  It was built outside of PBI since it has columns for our fiscal calendar, which is unusual (4-5-4, 52/53 calendar) and doesn't start on January 1.  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].  Because [Fiscal Year - Period Name] is a string, I have sorted the data in my date dimension table by a corresponding number column, i.e., [Fiscal Year - Period Number].  (It can't be sorted merely by [Date], since multiple [Date]s corresponding to a single value in [Fiscal Year - Period Name].)  When I create a line chart with axis of [Fiscal Year - Period Name], the sorting in the line chart is as expected.  When I create a line chart with axis of [Date], the sorting in the line chart is as expected.  (Albeit, PBI creates an automatic date hierarchy, which is useless to me.)  But, when I create a line chart with axis of the custom hierarchy I have defined, it is not sorting as expected.  Instead, it's sorting alphabetically.  I thought that the sorting of the Date dimension table by [Fiscal Year - Period Number] should have resolved this problem, even in the case of a custom hierarchy.

How can I resolve this sorting problem with my hierarchy?

[amitchandak]

The idle sorter is Date for any date or YYYYMMDD (MM can be period should always 2 digits, same with DD, it is a month day)

A sorted for the period is YYYYMM

You can use a text operator like format on the date, left, right and mid on text to construct these sort string

once these columns are created go and mark them as sort columns

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

I don't understand how this solves my problem.

[v-joesh-msft]

Hi @Anonymous ,

I'm a little confused by your description, sample data would help tremendously. If this is a problem like “sort by month”, you can take a look at the following post:

Sort By Month Names In Power BI

Sort Date Hierarchy

Note that you can add the Month number column through the Query Editor, but the sort itself is set in the report editor under the Modelling tab - the sort doesn't happen in Query Editor.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It turns out that the cause of my problem (and its solution) had nothing to do with the sorting procedure.  The procedure of the sorting (i.e., select field in the Report area, click on the Modeling tab, click on the 'Sort by Column' button, select [Fiscal Year - Period Number]) is correct.  Rather, the issue was my derivation of [Fiscal Year - Period Number].  I was erroneously generating values like 201920201 (for fiscal year 2019-2020, period 1) and 2019202012 (for fiscal year 2019-2020, period 12). The problem is that period 1 of the next fiscal year (i.e., 202020211) occurs after period 9 of the current fiscal year. That is, 2020211 comes after 201920209.  Hence, the sorting was working correctly, but wasn't what I expected.

One solution for this problem (the solution I used) is to take the fiscal year (where "-" is replaced by "" to make it numeric) and add to it the period number divided by 100. So, I now get values like 20192020.01 (for 2019-2020-P1), 20192020.12 (for 2019-2020-P12), and 20202021.01 (for 2020-2021-P1).

And, I also learned that the sorting cannot be implemented at the hierarchy level.  The sorting has to be done at the field level.  But, when implemented at the field level, the hierarchy inherits the sorting of its composite fields.