Issue with graph

[Robin73000]

Hello, 

I have an issue with my graph. 

I want to show in the same graph data for 3 years 2024 2025 and 2026. 

Howevre, i want one single bar for 2024 and 2025 and a monthly view for 2026, so 12 bars. 

When I use drill down, the drill down is done an all periods and i can't chose only a drill down for 2026. 

Do you know if it is possible to di that ? 

Thank you

[cengizhanarslan]

Step 1) Create a calculated column for display

In your Date table:

Axis Label :=
VAR YearNum = YEAR('Date'[Date])
RETURN
SWITCH(
    TRUE(),
    YearNum = 2026, FORMAT('Date'[Date], "MMM"),
    FORMAT('Date'[Date], "YYYY")
)

Step 2) Create a sort column

Axis Sort :=
VAR YearNum = YEAR('Date'[Date])
VAR MonthNum = MONTH('Date'[Date])
RETURN
IF(
    YearNum = 2026,
    202600 + MonthNum,   -- Jan=202601, Feb=202602...
    YearNum * 100        -- 2024 → 202400, 2025 → 202500
)

Then:

• Select Axis Label
• Sort by Axis Sort

Step 3)  Use this in your chart

Axis → Axis Label

Values → your measure

[BeaBF]

@Robin73000 Hi!

This is a known limitation in Power BI: drill-down is applied to the entire axis and cannot be restricted to a single year. As a result, it isn’t possible to expand only 2026 to the monthly level while keeping 2024 and 2025 aggregated when using the standard date hierarchy.

As a workaround, use a custom axis like this (calculated column in your calendar table):

Custom Axis =
IF(
'Calendar'[Year] = 2026,
FORMAT('Calendar'[Date], "MMM YYYY"),
FORMAT('Calendar'[Year], "0000")
)

Sort this column by this one:

Custom Axis Order =
IF(
'Calendar'[Year] = 2026,
YEAR('Calendar'[Date]) * 100 + MONTH('Calendar'[Date]),
'Calendar'[Year] * 100
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!