Compare any two years' data, with years chosen in slicers

[erbver]

Hello! 

I am trying to build a table (perhaps eventually other visuals) that allows a user to select any year as the current year and any year as the comparison year, and see columns for the current year's and comparison year's "portfolio scores" side-by-side. Ideally I would also have a column that calculates the score difference (not shown):

Sample data:

Portfolio Name	Year	Component	Aspect	Indicator	Indicator Name	Score Max	Portfolio Score
Portfolio 1	2021	Component A	Aspect A	Ind1	Indicator 1	5	4
Portfolio 1	2021	Component A	Aspect B	Ind2	Indicator 2	3	3
Portfolio 1	2021	Component B	Aspect A	Ind3	Indicator 3	6	5
Portfolio 1	2021	Component B	Aspect B	Ind4	Indicator 4	4	2
Portfolio 1	2022	Component A	Aspect A	Ind1	Indicator 1	5	4.5
Portfolio 1	2022	Component A	Aspect B	Ind2	Indicator 2	3	3
Portfolio 1	2022	Component B	Aspect A	Ind3	Indicator 3	6	5
Portfolio 1	2022	Component B	Aspect B	Ind4	Indicator 4	4	3
Portfolio 2	2021	Component A	Aspect A	Ind1	Indicator 1	5	3
Portfolio 2	2021	Component A	Aspect B	Ind2	Indicator 2	3	2
Portfolio 2	2021	Component B	Aspect A	Ind3	Indicator 3	6	4
Portfolio 2	2021	Component B	Aspect B	Ind4	Indicator 4	4	1
Portfolio 2	2022	Component A	Aspect A	Ind1	Indicator 1	5	3.5
Portfolio 2	2022	Component A	Aspect B	Ind2	Indicator 2	3	2.5
Portfolio 2	2022	Component B	Aspect A	Ind3	Indicator 3	6	4.5
Portfolio 2	2022	Component B	Aspect B	Ind4	Indicator 4	4	1.5

Aspect_Scores and Indicator_Scores contains data for both years, and more years will be added.

I tried following this video but I get the following error message.

If I add SUM or MIN like so:

Comparison Year Portfolio Score = CALCULATE(SUM(Indicator_Scores[Portfolio Score]),ALL('Current Year'),USERELATIONSHIP('Comparison Year'[Year],'Aspect_Scores'[Year]))

both the Portfolio Score and Comparison Year Portfolio Score columns change when I select the Comparison Year, but neither change when I select the Current Year.

I tried to experiment with using a calculated column row-by-row instead of a measure but wasn't able to get that to work either.

Thanks for your help!

[Mahesh0016]

@erbver  I hope this helps You! Thank You!!
Total Portfolio Score = SUM(Indicator_Scores[Portfolio Score])

Comparison Year Portfolio Score = 
CALCULATE(
                     [Total Portfolio Score]
                    ,ALL('Current Year')

                    ,USERELATIONSHIP('Comparison Year'[Year],'Aspect_Scores'[Year])
                  )

[erbver]

Hi @Mahesh0016, thank you for your help! Unfortunately it isn't working quite right. If I do your DAX as measures, Comparison Year Portfolio Score only shares rows with Total Portfolio Score if current year = comparison year. Otherwise, it duplicates all the rows (the first row is Indicator LE2):

If I do your DAX as calculated columns, the comparison year's values always equal the current year's values.