Create a comparative Matrix with data by year filtered with two slicers

Question

Hi,&nbsp;&nbsp; I am having trouble trying to create a matrix with the data filtered by two slicers (to compare the data by year).&nbsp;&nbsp; In other words, my data have a monthly summary of sales by product/year.&nbsp;&nbsp;FamilyDescriptionCodeDescriptionYearMonthAmountA01Bicycles 10 inchesA01-001Bicycles sport Red202112300A01Bicycles 10 inchesA01-001Bicycles sport Red202125000A01Bicycles 10 inchesA01-001Bicycles sport Red202135050A01Bicycles 10 inchesA01-001Bicycles sport Red202145100A01Bicycles 10 inchesA01-001Bicycles sport Red202155250A01Bicycles 10 inchesA01-001Bicycles sport Red202165300A01Bicycles 10 inchesA01-001Bicycles sport Red202175350A01Bicycles 10 inchesA01-001Bicycles sport Red202185400A01Bicycles 10 inchesA01-001Bicycles sport Red202196300A01Bicycles 10 inchesA01-001Bicycles sport Red2021106350A01Bicycles 10 inchesA01-001Bicycles sport Red2021116400A01Bicycles 10 inchesA01-001Bicycles sport Red2021126450A01Bicycles 10 inchesA01-001Bicycles sport Red202216500A01Bicycles 10 inchesA01-001Bicycles sport Red202226550A01Bicycles 10 inchesA01-001Bicycles sport Red202236000A01Bicycles 10 inchesA01-001Bicycles sport Red202247300. . .. . .. . .. . .. . .. . .. . .A01Bicycles 10 inchesA01-001Bicycles sport Red202338900A01Bicycles 10 inchesA01-002Bicycles sport Black202112300A01Bicycles 10 inchesA01-002Bicycles sport Black202125000. . .. . .. . .. . .. . .. . .. . .A01Bicycles 10 inchesA01-002Bicycles sport Black202326000A01Bicycles 10 inchesA01-002Bicycles sport Black202337300A02Bicycles 12 inchesA02-001Bicycles sport Red202113000A02Bicycles 12 inchesA02-001Bicycles sport Red202123450. . .. . .. . .. . .. . .. . .. . .A02Bicycles 12 inchesA02-001Bicycles sport Red202325600A02Bicycles 12 inchesA02-001Bicycles sport Red202335666A02Bicycles 12 inchesA02-002Bicycles sport Black202112300A02Bicycles 12 inchesA02-002Bicycles sport Black202125000. . .. . .. . .. . .. . .. . .. . .A02Bicycles 12 inchesA02-002Bicycles sport Black202325466A02Bicycles 12 inchesA02-002Bicycles sport Black202336000&nbsp;&nbsp;I have a slicer to select a first year to compare and another slicer to select the second year to compare (This second slicer has a disconnected table of years and i used the command&nbsp;USERELATIONSHIP).&nbsp;It works fine to create a BAR CHART, but now the user needs a matrix to compare both years values by month and calculate a variation&nbsp;Something like this:First year selection: 2021Second year selection: 2022&nbsp;Result:&nbsp; &nbsp;&nbsp;&nbsp;JanFebMarapr20219900184505050….2022207221226000….VARIATION782816328-950&nbsp;&nbsp;&nbsp;Is it Possible??.&nbsp;I tried using the command inscope, but i can show only 1 year.&nbsp;Thank you in advance&nbsp;

anonymous · Accepted Answer

Hi&nbsp;gomezc73&nbsp;,
&nbsp;
I suggest you to inactive all relationships betwen two dimyear tables and your data table.
Data model:

Measure:
Measure = 
VAR _RecentYear =
    SELECTEDVALUE ( 'Year'[Year 1] )
VAR _PreviousYear =
    SELECTEDVALUE ( 'Year 2'[Year 2] )
VAR _Amount =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Year] IN { _PreviousYear, _RecentYear } )
    )
VAR _Part1 =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Year] = _RecentYear )
    )
VAR _Part2 =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Year] = _PreviousYear )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[Year] ), _Amount, _Part2 - _Part1 )
Result is as below.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

	Jan	Feb	Mar	apr
2021	9900	18450	5050	….
2022	2072	2122	6000	….
VARIATION	7828	16328	-950

Forum Discussion

Create a comparative Matrix with data by year filtered with two slicers

2 Replies

Related content

Slicer for filtering several columns

Time Filter Slicer

Filter data using slicer

Slicer Filtering Another slicer

Power BI Dynamic Filter/Slicer

Family	Description	Code	Description	Year	Month	Amount
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	1	2300
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	2	5000
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	3	5050
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	4	5100
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	5	5250
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	6	5300
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	7	5350
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	8	5400
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	9	6300
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	10	6350
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	11	6400
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2021	12	6450
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2022	1	6500
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2022	2	6550
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2022	3	6000
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2022	4	7300
. . .	. . .	. . .	. . .	. . .	. . .	. . .
A01	Bicycles 10 inches	A01-001	Bicycles sport Red	2023	3	8900
A01	Bicycles 10 inches	A01-002	Bicycles sport Black	2021	1	2300
A01	Bicycles 10 inches	A01-002	Bicycles sport Black	2021	2	5000
. . .	. . .	. . .	. . .	. . .	. . .	. . .
A01	Bicycles 10 inches	A01-002	Bicycles sport Black	2023	2	6000
A01	Bicycles 10 inches	A01-002	Bicycles sport Black	2023	3	7300
A02	Bicycles 12 inches	A02-001	Bicycles sport Red	2021	1	3000
A02	Bicycles 12 inches	A02-001	Bicycles sport Red	2021	2	3450
. . .	. . .	. . .	. . .	. . .	. . .	. . .
A02	Bicycles 12 inches	A02-001	Bicycles sport Red	2023	2	5600
A02	Bicycles 12 inches	A02-001	Bicycles sport Red	2023	3	5666
A02	Bicycles 12 inches	A02-002	Bicycles sport Black	2021	1	2300
A02	Bicycles 12 inches	A02-002	Bicycles sport Black	2021	2	5000
. . .	. . .	. . .	. . .	. . .	. . .	. . .
A02	Bicycles 12 inches	A02-002	Bicycles sport Black	2023	2	5466
A02	Bicycles 12 inches	A02-002	Bicycles sport Black	2023	3	6000