Variance Matrix based off slicer selection

Hi jkendalljones 

Thanks for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario with a sample dataset. It worked fine for me . I hope this will work for you as well . You can try the following steps to resolve the issue.

Instead of using two visuals try taking everything in a single visual . Here are the steps:

Step 1: Create the Date Table

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 the start and end dates accordingly

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

    "Year", YEAR([Date]),

    "Month", MONTH([Date])

)

This Date table will allow you to create month-year combinations for slicers. It also includes separate columns for Month and Year, which will be useful for the calculations.

Step 2: Create Relationships

     You should create a single relationship between the DateTable[Date] and the Balance     Sheet[Date] column.

   This relationship will allow the slicer to filter the data based on the selected month-year in DateTable[Date].

Step 3: Create Variance Calculation Using DAX

Now, we need to create a DAX measure to calculate the variance between the adjacent months based on the slicer selection.

DAX

Variance Amount =

VAR MinMonth = MIN(DateTable[MonthYear])  -- Get the earliest selected month

VAR MaxMonth = MAX(DateTable[MonthYear])  -- Get the latest selected month

VAR MinMonthAmount =

    CALCULATE(

        SUM('Balance Sheet'[Total]),

        FILTER(

            ALL(DateTable),

            DateTable[MonthYear] = MinMonth

        )

    )

VAR MaxMonthAmount =

    CALCULATE(

SUM('Balance Sheet'[Total]),

        FILTER(

            ALL(DateTable),

            DateTable[MonthYear] = MaxMonth

        )

    )

RETURN

    MaxMonthAmount - MinMonthAmount  -- Return the variance between first and last selected months

Step 4: Set Up the Slicer

• Slicer: Create a slicer using DateTable[MonthYear] to allow the user to select one or more months.
• Multiple Selection: If you want the user to select more than one month, enable multi-select in the slicer. If you don’t find Selection Controls is unavailable, you manually select multiple items by holding down the Ctrl key while clicking on items in the slicer.

Step 5: Set Up the Matrix Visual

• Matrix Visual:
• In the Rows section, add the Category field (or other relevant dimensions).
• In the Values section, add the Variance Amount measure.

Step 6: Test the Solution

1. Select different months in the MonthYear slicer.
2. The Variance Amount measure should dynamically calculate and show the difference between the selected months.
3. If multiple months are selected, it will calculate the difference between the current and previous month, as per your slicer selection.

Have a glance at the below pictures.

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.