Matrix with Year and Month Column

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,