Strange Sorting Behavior in Power BI Calendar Table – year_month_short vs. month_year_short

[AugustoChaves]

Hi everyone,

We’re seeing a strange issue in our Power BI semantic model and would appreciate any insights.

We have a calendar table in our warehouse that includes two formatted string columns:

• year_month_short (e.g., Jan/2025)
• month_year_short (e.g., 2025/Jan)

Both are derived from the same calendar_date column. When we sort visuals by calendar_date or even by a numeric surrogate key (datekey_1), both of these columns start showing repeated values—each calendar_date ends up displaying the same year_month_short or month_year_short multiple times.

However, if we sort by a different column like month_end, the issue disappears and the values display correctly.

We’ve double-checked the data and sort-by columns, and everything seems in order.

Has anyone encountered this kind of behavior before? Could this be a Power BI rendering or model relationship issue?

Any ideas or workarounds would be greatly appreciated!

[Deku · Accepted solution]

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

[V-yubandi-msft]

Hi @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'.

[rohit1991 · Accepted solution]

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.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[Deku · Accepted solution]

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