How to filter a page by the same column as min and max?

[Cbutler]

I am building a report based on the change in Welsh language speakers (by local authority(LA)) from the Census, inc years 1991, 2001, 2011 and 2012. 

I have this variable to calc the % diff, as you can see the Min and Max of the year is working off the same column. I want the end user to be able to determine what the min and max years are - eg, be able to dynamically compare 1991 with 2001, or 2011 with 2021, or 2001 and 2021 etc..... but I can't work out how to do this with the slicers on the page as the values are off the same column. 

I have tried a between slicer - but the issue is that this runs all the values between 1991 and 2021, and I don't want people to be able to pick 1997 for example as it will not be clear that this is the figure from 1991.

% of Welsh speakers = sum('LA, age and year'[Welsh Speakers])/sum('LA, age and year'[Persons])

Variable % change welsh speakers =

VAR Min_Year =

    CALCULATE (

        [% of Welsh speakers],

        'LA, age and year'[Year] IN { MIN ( 'LA, age and year'[Year]) }

    )

VAR Max_Year =

    CALCULATE (

        [% of Welsh speakers],

        'LA, age and year'[Year] IN { MAX ( 'LA, age and year'[Year] ) }

    )

RETURN

    Max_Year - Min_Year

Any advice how to allow the Min and Max year to be filtered on the page so that the variable dynamically changes? 

Dataset layout 

Year	LA	Age	Persons	Welsh Speakers
2021	Blaenau Gwent	16-39	19185	1614
2011	Blaenau Gwent	16-39	20889	1578
2001	Blaenau Gwent	16-39	21515	1256
1991	Blaenau Gwent	16-39	23778	425
2001	Bridgend	16-39	39839	3729
2011	Bridgend	16-39	40925	4380
2021	Bridgend	16-39	41011	4684
1991	Bridgend	16-39	43034	2537
2021	Caerphilly	16-39	50129	6690
2011	Caerphilly	16-39	53510	6616
2001	Caerphilly	16-39	53993	5354
1991	Caerphilly	16-39	57862	2919
1991	Cardiff	16-39	102671	6712
2001	Cardiff	16-39	114691	12073
2011	Cardiff	16-39	138626	15086
2021	Cardiff	16-39	141618	17118
2021	Carmarthenshire	16-39	47746	19067
2001	Carmarthenshire	16-39	48213	21264
2011	Carmarthenshire	16-39	48673	19847
1991	Carmarthenshire	16-39	50438	23974
1991	Ceredigion	16-39	18989	10737
2021	Ceredigion	16-39	20873	9085
2001	Ceredigion	16-39	24018	10796
2011	Ceredigion	16-39	24625	10057
2021	Conwy	16-39	26901	8229
2011	Conwy	16-39	27827	8564
2001	Conwy	16-39	28985	8734
1991	Conwy	16-39	30307	8605

@Greg_Deckler any ideas? 

[amitchandak]

@Cbutler , Based on what I got, Create a table with the distinct year , join with year of your table

new measure =
var _max = maxx(allselected(Year),Year[Year])
var _min = minx(allselected(Year),Year[Year])
return
calculate( sum(Table[Value]), filter('Year', 'Year'[Year] =_max)) - calculate( sum(Table[Value]), filter('Year', 'Year'[Year] =_min))

[Cbutler]

Thansk @amitchandak , Could you give me a link to your pbix. file? I tried something similar and it didn't work.