Matrix With Multiple Values on columns (Month & Quarter side by side )

Hi pmargari ,

 

Since you want to achieve month and quarter independence, you need to create two separate calendars. Note that there can be no relationship between these three tables (the main table, the two calendar tables). Then because there may be multiple years in the data, it is easy to confuse the separate filtering of the year and month, so I combined the month and the year to filter, and the quarter year is also the same.

Sample data:

Two calcualted tables:

Calendar1 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "CalendarMonthYear", FORMAT ( [Date], "MM-YYYY" )
)

Calendar2 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "Calendar1QuarterYear", FORMAT ( [Date], "Q-YYYY" )
)

No relationship.

Then MonthSales measure and QuarterSales measure are as follows.

MonthSales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        FORMAT ( [Date], "MM-YYYY" ) IN ALLSELECTED ( 'Calendar1'[CalendarMonthYear] )
    )
)

QuarterSales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        FORMAT ( [Date], "Q-YYYY" ) IN ALLSELECTED ( 'Calendar2'[Calendar1QuarterYear] )
    )
)

The following results show that MonthSales are not affected by the Quarter filter. The Quarter filter filters the second quarter (April, May, June), but MonthSales returns January sales because the Month filter filters January. You can choose multiple of these two filters.

 

 

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.