Forum Discussion
Advocate IIStrange Sorting Behavior in Power BI Calendar Table – year_month_short vs. month_year_short
This is as expected. When you sort a column by another column, both are included in every query. This means you see the year month duplicate once per date. You really want to sort the column by a field that has the same frequency of change. The month end works since you only have a single value of month end per month. So you want to create a number field of year number and month number, which can be used as the sort by column
Hi AugustoChaves ,
The behavior you're observing in Power BI with the year_month_short and month_year_short fields showing repeated values when sorted by calendar_date is actually expected due to how Power BI's "Sort by Column" functionality works. When you set a column (like year_month_short) to be sorted by another column (like calendar_date), Power BI includes both fields in the query context.
Since calendar_date is at the daily granularity and your formatted strings represent monthly granularity, Power BI displays a row for each date, causing apparent duplication of month names.
Your observation that sorting by a column like month_end resolves the issue makes sense — that field likely has one unique value per month, aligning with the granularity of your display fields. The best practice here is to create a numeric column that represents the month in a sortable way — for example, a YYYYMM integer (like 202812 for December 2028) — and use that as your “Sort by Column”. This keeps the frequency of change consistent between the display column and the sort column, avoiding unnecessary duplication.
Community SupportHi AugustoChaves ,
Thanks for posting your query in the Microsoft Fabric Community. I tested the issue on my end and was able to replicate it exactly as you described.
You are correct - when visuals use daily granularity (like calendar_date), monthly fields such as year_month_short or month_year_short can appear duplicated.
As the rohit1991 , pointed out, this happens due to a granularity mismatch. Here's how I resolved it.
1.Created a new column to represent months as sortable integers
MonthSort = YEAR([calendar_date]) * 100 + MONTH([calendar_date])
2. Set year_month_short to Sort by column - MonthSort to ensure proper ordering.
3. Used year_month_short in visuals without including calendar_date, preventing duplication.
FYI:
This approach ensures proper sorting at the month level and eliminates the repeated month labels caused by daily granularity. For your reference, I’ve attached the PBIX file with this setup implemented.
Thanks for your inputs rohit1991 .
If my response solved your query, please mark it as the Accepted solution to help others find it easily. And if my answer was helpful, I'd really appreciate a 'Kudos'.