Greater than or less than Slicer

[LadaA]

Hi everyone,

The data I am working with is in the format of "Start Date" and "End Date". Is there any way that I could make it so that I have a slicer ranging from say 2015-2030 and if the slicer is set to 2023 it will show the data of all the projects that will be active that year.

Example of Data:

Plant	Plant Name	Product Type	Start Date	End Date	Annual LB Produced
1	A	Fruit	1/11/2019	11/1/2036	47563
2	B	Meat	1/1/2019	6/1/2023	22128
3	C	Fruit	11/1/2023	11/1/2035	97860
4	D	Vegies	1/1/2025	12/1/2034	24650
5	E	Grain	4/1/2024	1/1/2037	272804
6	F	Meat	11/1/2022	6/1/2023	36542

In the case mentioned above where the slicer is set 2023 any data mapped to Plants 1,2,3 and 6 would be visable.

Any help would be greatly appreciated and thank you in advance for your time!

[devanshi]

General = FILTER(
                        AND( GENERATESERIES(YEAR(MIN([StartDate]), YAER(MAX[ENDDATE])),
                                   YEAR([StartDate)<=YEAR([EndDate])
                                   )
                       )

[barritown · Accepted solution]

Hi @LadaA,

If you have a relatively small dataset, you can create an extended table like the one below and use the newly added column in it for filtering. If you deal with big data, you'll need another, less gready solution.

In plain text:

Extended Data = 
FILTER (
    CROSSJOIN ( 
        data,
        GENERATESERIES ( YEAR ( MIN ( data[Start Date] ) ), YEAR ( MAX ( data[End Date] ) ), 1 ) ),
    [Value] >= YEAR ( [Start Date]) && [Value] <= YEAR ( [End Date] ) ) 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[LadaA]

Thank you @barritown ,

Thankfully my dataset is pretty small so this worked wonders!