Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
TomasGazsi
Frequent Visitor

How to create a matrix with calculated values ​​based on a selection of years from a slicer?

Hi, 

I create a "total operating result" report for the customer by individual months and years.
I have gone through several tutorials that addressed the issue at hand but have not achieved a satisfactory result.


To simplify the assignment, I created sample data, which I will use to explain the needs.

 

  1. I have generated a test data table, which contains only 2 columns, which are Date and TotalWithoutVAT. Each month has 2 records. It is generated from the year 2023 to the year 2026.
    TomasGazsi_0-1738144098081.png
  2. I have generated a table Tabl_kalendar_NEW. Tabl_kalendar_NEW = CALENDAR(DATE(2023,01,01),DATE(2026,12,31))
  3. I added columns to it (I don't know if it's necessary):
    Year: Year = YEAR(Tabl_kalendar_NEW[Date])
    Month: Month = FORMAT(Tabl_kalendar_NEW[Date], "MMMM")
    MonthNum: MonthNum = MONTH(Tabl_kalendar_NEW[Date])

  4. I sorted the table by the YEAR column.
    TomasGazsi_1-1738144165906.png

     

  5. Then I linked / connected the table Tabl_kalendar_NEW with test_table
     
    TomasGazsi_0-1738144332120.png
  6. Then I started creating the measures and matrix.

    Measures:

    1. SumPriceWithoutVAT Year1 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MIN(CalendarTable_NEW[Date].[Year]))

    2. SumPriceWithoutVAT Year2 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MAX(CalendarTable_NEW[Date].[Year]))

    3. Interannual Difference_V3 =

      VAR Year1 = CALCULATE( [Sum_PriceWithoutVAT],
        FILTER(
            ALL(Table_calendar_NEW),
            Tabl_kalendar_NEW[YEAR] = MIN(Tabl_kalendar_NEW[YEAR])
            )
      )
      VAR Year2 = CALCULATE( [Sum_PriceWithoutVAT],
          FILTER(
             ALL(Table_calendar_NEW),
             Tabl_kalendar_NEW[YEAR] = MAX(Tabl_kalendar_NEW[YEAR])
          )
      )
      RETURN
      Year2 - Year1
  7. Alternative difference calculations (Measures):
    1. Interannual Difference =
      VAR SelectedYears = VALUES(Calendar_Table_NEW[YEAR])
      VAR Year1 = MINX(SelectedYears, Calendar_Table_NEW[YEAR]) -- Select year 1 by slicer
      VAR Year2 = MAXX(SelectedYears, Calendar_Table_NEW[YEAR] ) -- Select year 2 by slicer
      VAR TotalYear1 =
            CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year1)
      VAR TotalYear2 =
            CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year2)
      RETURN
      SumYear2 - SumYear1

    2. calculated_year-to-year_difference_in_total_V2 = [SumPriceWithoutVAT Year2] - [SumPriceWithoutVAT Year1]

  8. Matrix: the problem is that the differences keep returning 0. But the bottom table (not the matrix) calculates it correctly. I can't identify where I might be making a mistake. Or is it necessary to set up the matrix specifically? Or is the problem in the connection/settings of the calendar table and the test table?
    TomasGazsi_1-1738144404444.pngMatrix legend:
        Rows - months
        Columns - years
        Values:
           Column 1 - SumPriceWithoutVAT Year1
           Column 2 - SumPriceWithoutVAT Year2
           Column 3 - Interannual Difference_V3
           Column 4 - calculated_year-to-year_difference_in_total_V2
           Column 5 - Interannual Difference

  9. Setting up the bottom table:
    Here you can see that selecting years from the slicer works. Also the sum of prices excluding VAT works. And also the calculation of the difference between Year2 and Year1 works.
    TomasGazsi_2-1738144436565.png

 

Could you please advise me where I might be making a mistake? Why doesn't the matrix behave "similarly" to the table - considering the selection of years from the slicer?
I would be very grateful for any advice on how I can do calculations while the user selects the year according to his/her own wishes


Thanks a lot Tom.

1 ACCEPTED SOLUTION
v-linyulu-msft
Community Support
Community Support

Hi, @TomasGazsi 
Thanks for reaching out to the Microsoft fabric community forum.

The reason for this issue is that in the matrix, you have set the column to year, so your output results include a year filter. This causes the variable filter in the measure to be empty. In your table visual object, there is no year, so the output is normal. My suggestion is to create an unrelated date table to use as the rows and columns in the matrix, and replace the values part with measures:

 

1.First, create a calculated table, then add it to the matrix, using the year from the calculated table as a slicer:

 

Table = CALENDAR(DATE(2023, 01, 01), DATE(2026, 12, 31))

 

 

vlinyulumsft_0-1738224390663.png

 

2.Then, use the following two measures as values:

 

Normal total = 
CALCULATE (
    SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
    FILTER (
        ALLSELECTED ( Tabl_kalendar_NEW ),
        YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
            && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                = SWITCH (
                    MAX ( 'Table'[Date].[Month] ),
                    "January", 1,
                    "February", 2,
                    "March", 3,
                    "April", 4,
                    "May", 5,
                    "June", 6,
                    "July", 7,
                    "August", 8,
                    "September", 9,
                    "October", 10,
                    "November", 11,
                    "December", 12
                )
    )
)
diff = 
VAR mid1 =
    MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR mad2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR count1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
VAR f =
    IF ( count1 = 2, mad2 - mid2, BLANK () )
RETURN
    f

 

3.Here's my final result, which I hope meets your requirements.

20250120-0630-35 (online-video-cutter.com) (4).gif

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

View solution in original post

5 REPLIES 5
TomasGazsi
Frequent Visitor

@v-linyulu-msft Thank you so much!!
It helped me solve my problem that I had been struggling with for several days.

v-linyulu-msft
Community Support
Community Support

Hi, @TomasGazsi 
Thanks for reaching out to the Microsoft fabric community forum.

The reason for this issue is that in the matrix, you have set the column to year, so your output results include a year filter. This causes the variable filter in the measure to be empty. In your table visual object, there is no year, so the output is normal. My suggestion is to create an unrelated date table to use as the rows and columns in the matrix, and replace the values part with measures:

 

1.First, create a calculated table, then add it to the matrix, using the year from the calculated table as a slicer:

 

Table = CALENDAR(DATE(2023, 01, 01), DATE(2026, 12, 31))

 

 

vlinyulumsft_0-1738224390663.png

 

2.Then, use the following two measures as values:

 

Normal total = 
CALCULATE (
    SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
    FILTER (
        ALLSELECTED ( Tabl_kalendar_NEW ),
        YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
            && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                = SWITCH (
                    MAX ( 'Table'[Date].[Month] ),
                    "January", 1,
                    "February", 2,
                    "March", 3,
                    "April", 4,
                    "May", 5,
                    "June", 6,
                    "July", 7,
                    "August", 8,
                    "September", 9,
                    "October", 10,
                    "November", 11,
                    "December", 12
                )
    )
)
diff = 
VAR mid1 =
    MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR mad2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR count1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
VAR f =
    IF ( count1 = 2, mad2 - mid2, BLANK () )
RETURN
    f

 

3.Here's my final result, which I hope meets your requirements.

20250120-0630-35 (online-video-cutter.com) (4).gif

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

@v-linyulu-msft And I see that the TOTAL row is average, but can I change it to SUM the whole year? Thanks

Hi, @TomasGazsi 
Thank you for your prompt response and for accepting my idea as the solution. Regarding your question, you just need to add an additional check using ISINSCOPE(). I have modified the measure as follows:

diff = 
VAR mid1 =
    MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR mad2 =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
VAR count1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        count1 = 2,
        IF (
            ISINSCOPE ( 'Table'[Date].[Month] ),
            mad2 - mid2,
            CALCULATE (
                SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
                FILTER (
                    ALLSELECTED ( Tabl_kalendar_NEW ),
                    YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
                )
            )
                - CALCULATE (
                    SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
                    FILTER (
                        ALLSELECTED ( Tabl_kalendar_NEW ),
                        YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
                    )
                )
        ),
        BLANK ()
    )
Normal total = 
VAR ff =
    CALCULATE (
        SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
        FILTER (
            ALLSELECTED ( Tabl_kalendar_NEW ),
            YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
                && MONTH ( 'Tabl_kalendar_NEW'[Date] )
                    = SWITCH (
                        MAX ( 'Table'[Date].[Month] ),
                        "January", 1,
                        "February", 2,
                        "March", 3,
                        "April", 4,
                        "May", 5,
                        "June", 6,
                        "July", 7,
                        "August", 8,
                        "September", 9,
                        "October", 10,
                        "November", 11,
                        "December", 12
                    )
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Date].[Month] ),
        ff,
        CALCULATE (
            SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
            FILTER (
                ALLSELECTED ( Tabl_kalendar_NEW ),
                YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
            )
        )
    )

Here is the current result: 

20250120-0630-35 (online-video-cutter.com).gif

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

Thanks again @v-linyulu-msft , this solution helped!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.