Forum Discussion
date hierarchy sorting
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
I don't understand how this solves my problem.
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
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.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.
