Variance Matrix based off slicer selection

Hello jkendalljones 
Hope you are doing well!

I understand the issue regarding matrix variance .I’ve found a solution that meets your requirements. Here are the modified steps :

1.First, create a Date table that will represent the months you want to use for comparison. This Date table will be used by the slicer to allow the user to select different months.

DAX FORMULA

DateTable =

ADDCOLUMNS (

    CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)),  -- Adjust dates as needed

    "MonthYear", FORMAT([Date], "MMM YYYY"),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]))

2.Now create two duplicate date tables Duplicate the DateTable twice and name them.

• DateTable1
• DateTable2

3.Create inactive relationships between these tables and the Balance Sheet table:

• DateTable1[Date] → Balance Sheet[Date]
• DateTable2[Date] → Balance Sheet[Date]

4.Active Relationship for Variance Matrix: Keep the active relationship between the main DateTable and the Balance Sheet[Date].

5. Add Slicers for Date Selection(Place two slicers on the page):

• Slicer 1: DateTable1[MonthYear] (for Matrix 1).
• Slicer 2: DateTable2[MonthYear] (for Matrix 2).

6. Create a DAX Measures for Matrix1 , Matrix2 and Variance Matrix individually.

     1. Measure for Matrix 1

        Calculate the total for Matrix 1 based on DateTable1 slicer:

        DAX

        Total Matrix 1 =

        CALCULATE(

        SUM('Balance Sheet'[Total]),

        USERELATIONSHIP('Balance Sheet'[Date], 'DateTable1'[Date]))

       2. Measure for Matrix 2

           Calculate the total for Matrix 2 based on DateTable2 slicer:

           DAX  

           Total Matrix 2 =

           CALCULATE(

           SUM('Balance Sheet'[Total]),

           USERELATIONSHIP('Balance Sheet'[Date], 'DateTable2'[Date]))

         3. Variance Measure

            Calculate the variance between the two selected months:

            Variance Amount =

            [Total Matrix 1] - [Total Matrix 2]

7.Set Up Visuals

1. Matrix 1 (First Month)[Rows: Category ,Values: Total Matrix1].
2. Matrix 2(Second Month)[Rows: Category,Values: Total Matrix 2].
3. Matrix 3 (Variance Matrix)[Rows: Category,Values: Variance Amount].

8.Step 6: Test Interactions

1. Go to Format → Edit Interactions to ensure:
• Slicer 1 only affects Matrix 1.
• Slicer 2 only affects Matrix 2.
• Neither slicer directly affects the variance matrix (it updates dynamically through the measures).

Please check the pictures below:

I hope you will get the solution as per the requirements you mentioned above.If you’re still experiencing issues, feel free to reach out to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.