Variance Matrix based off slicer selection

[jkendalljones]

Screenshot Attached - I have two very simple matrix’s (I've blacked out the values in the screenshot) from the same data source (and table) on the same page that have a Category field and an Amount field, but each matrix has its own Date (specifically month) slicer.

I need to create a third identical matrix on the same page that displays the variance amounts being displayed in the two matrix’s depending on the months that are selected in each slicer. (For ex: if the user selects January in the month slicer above Matrix 1 and April in the month slicer above Matrix 2, i would like this new matrix to show the variances between January and April). Is this doable? If so, how would I go about doing it?

Note: I have no problem creating this variance matrix using its own independent slicers, but I would like it to work so that the user only has to change the slicers one time.

I tried several things, including using separate date fields for each slicer and SelectedValue in a DAX formula to calculate the variance amount like below:

variance amount = 
    calculate(sum('Balance Sheet'[Total]),'Balance Sheet'[Date] = SELECTEDVALUE('Balance Sheet'[Date]))
-   calculate(sum('Balance Sheet'[Total]),'Balance Sheet'[Date] = SELECTEDVALUE('Balance Sheet'[Date 2]))

however I am a DAX beginner and I ran into issues due to the slicers on the page (and when I turn off edit interactions then SelectedValue stops working). This seems impossible - any advice is greatly appreciated!

(Also - I have full control over the data source and can reconfigure things if needed).

[v-karpurapud]

Hi @jkendalljones 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

If it is worked for you , please mark it as Accept as Solution and give a Kudos.

Your feedback is important to us, Looking forward to your response. 

[v-karpurapud]

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.

[jkendalljones]

I sincerely appreciate the thorough response!

Unfortunately, my main issue is that I need the 3rd variance matrix to be on the same powerbi page as the other two matrix's, and I need those two primary matrix's to be displaying the dates selected by the user in the slicer(s).

Example using your 1st screenshot: I would need Matrix 1 to be displaying April 2023 values, Matrix 2 to be displaying July 2024 values, and matrix 3 to be displaying the variance between the two selected MonthYears all on the same page. 

[v-karpurapud · Accepted solution]

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.

[jkendalljones]

Hello,

Thank you so much for your assistance! I followed every step very closely and double checked everything and for some reason my variance matrix is not updating dynamically based off the slicer selections - it just says 0 in every category.

I attached a few screenshots in case you can see I am doing anything incorrectly - but I am pretty certain I followed all of the steps.

[v-karpurapud]

Hi @jkendalljones 
Hope you are doing well!
As I was going through your last message, I was unable to find the mistake in the screenshots provided by you. As the steps done by you are correct and still you are unable to get the solution, I would like to request you to share your PBIX file so that I can cross verify and get a better understanding of all the steps done by you and this way I will be able to give you a better solution.

[jkendalljones]

Hello,

I decided to try all of your steps again with a brand new file and it worked perfectly as intended!! Thank you SO SO much for your help - I really appreciate it!

[lbendlin]

Put everything into a single visual. Use a slicer to allow the user to select two (or more) items).  Use Visual Calculations to compute the variance between adjacent columns.

[jkendalljones]

I've never used visual calculations - do you mind elaborating a bit on how exactly I would use it in this scenario?