Matrix with Year and Month Column

[RichardsonMM]

I would like to make this excel table in Power BI, however i find some problems doing it, like, how can i use both year and month in the column of my matrix? I don't want to create two separated matrix, because the rows can be expanded to a lower hierarchy level, and when i expand in one matrix, the other would not.

[Anonymous]

Hi @RichardsonMM,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[Anonymous]

Hi @RichardsonMM,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Anonymous]

Hi @RichardsonMM,

Thank you for reaching out in Microsoft Community Forum.

Thank you @krishnakanth240, @DataNinja777    for the helpful response.

As suggested by Krishnakanth240, DataNinja777., I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[krishnakanth240 · Accepted solution]

Hi @RichardsonMM ,

You can modify the DAX formula for your calendar table:

Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"PeriodLabel",
IF(YEAR([Date]) < 2025,
FORMAT([Date], "yyyy"),
IF(MONTH([Date]) = 1, FORMAT([Date], "yyyy"), FORMAT([Date], "yyyy/mm"))
),
"PeriodSort",
IF(YEAR([Date]) < 2025,
YEAR([Date]) * 100,
IF(MONTH([Date]) = 1, YEAR([Date]) * 100, YEAR([Date]) * 100 + MONTH([Date]))
)
)

[DataNinja777]

Hi @RichardsonMM ,

The best practice for replicating your Excel layout in Power BI is to use a dedicated calendar table with a dynamic column that combines year-level and month-level granularity in a single field. This allows you to display both annual totals like “2024” and monthly breakdowns like “2025/01” in one unified matrix visual, with consistent drill-down and row expansion behavior.

Create your calendar table using this DAX formula:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "PeriodLabel", 
        IF(YEAR([Date]) < 2025, FORMAT([Date], "yyyy"), FORMAT([Date], "yyyy/mm")),
    "PeriodSort", 
        IF(YEAR([Date]) < 2025, YEAR([Date]) * 100, YEAR([Date]) * 100 + MONTH([Date]))
)

After creating the table, mark it as a Date Table using the [Date] column. In your matrix visual, drag PeriodLabel to the Columns section, your account hierarchy to Rows, and your value measures (e.g. “Receita Bruta”, “Devoluções de Venda”) to Values. Then, sort PeriodLabel by PeriodSort to maintain the correct chronological order.

This setup ensures a single matrix visual supports both year and month views dynamically, without splitting visuals, and aligns with time intelligence best practices in Power BI.

Best regards,

[RichardsonMM]

The problem is that when it's 2025, i want to show on the matrix the year total and also year/month, this solution only shows 

2024 2025/01 2025/02

I need:

2024 2025 2025/01 2025/02