Calculate maximum value of a category for a year and its previous year value

[AnthonyJoseph]

Hello Community,

I have a table (Table1). I have two slicers "Region" and "Year". I need to create a bar graph which displays the aggregated maximum value of a particular category for the selected year along with the pervious year value of that category based on the "Year" and "Region" selected from slicers.  Below is the table and the expected output.

Table1

Id	Attribute	Category	Year	Value	Region
bf210976	1	A	2020	100	APAC
b269211c	1	C	2020	30	EMEA
b3ae147b	2	E	2020	45	AMER
8c850f82	1	E	2020	34	EUR
e1678c76	5	R	2020	56	EUR
517e463b	1	B	2021	20	AMER
ac5bb55b	2	D	2021	10	APAC
454c8978	4	Q	2021	70	EMEA
69bf32d8	5	Y	2021	78	APAC
6abf32d8	1	A	2021	90	APAC
27a48ee4	2	B	2021	18	EMEA
f46b64c1	4	C	2021	33	AMER
ecef8a75	5	R	2021	44	AMER
f90970d4	7	U	2022	150	EMEA
08c3e5ba	3	R	2022	34	APAC
92e24e31	4	A	2022	12	EMEA

For example: If the year is selected as 2021 then output should be:

Example 2:

if the year 2022 is selected then output should be:

These charts should be dynamic based on the "Year" and "Region" slicer selection. It would be really great if someone can help me hear.

Thanks,

Anthony Joseph

[tackytechtom · Accepted solution]

Hi @AnthonyJoseph ,

I tried to simulate the issue, but I do not understand how you get 140 for A in the year 2021. The only row I could find that matches that requirement in your data is:

Can you explain how get to the 140? 🙂 I might have misunderstood your query...

[Edit]

Here one that might come close to what you would like to achieve:

Here the code for the measures:

SumValueSelectedYear = 
VAR _selectedYear = MAX ( Table[Year] )
RETURN
CALCULATE (
    SUM ( Table[Value] ),
    Table[Year] = _selectedYear
)

SumValueSelectedYearMinus1 = 
VAR _selectedYear = MAX ( Table[Year] ) - 1
RETURN
CALCULATE (
    SUM ( Table[Value] ),
    REMOVEFILTERS ( Table[Year] ),
    Table2[Year] = _selectedYear
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[AnthonyJoseph]

Thanks @tackytechtom. You are correct! My bad it should 90 not 140 🙂 Thank you so much for helping me.

Also, just wanted to check if there is any way that we can display the values for the category of the selected year instead of prior year. 

For example; in the years 2020, 2021, 2022

When I select 2022 and 2020, I should see the top 3 values by categories for 2022 and their corresponding values for 2020 (instead of prior year). Similarly when I select 2022, 2020 and 2019. Visual should display the top 3 categories in 2022 and their category value for 2020 and 2019. Please can you help me to achieve this.

Really appreciate your help.

Thanks,

Anthony Joseph