filter for values for all years after starting year

[Anonymous]

dear all,

I have an ID that has an starting date and ending date. I would like to have a year filter on my report which filters all the values that are between (or equal <= and >=).

How can i do this?

best

[Anonymous]

To further extend my explanation, i hereby have an example of what my data looks like:

ID	Start	End
A	1-1-2015	31-12-2018
B	1-6-2017	null
C	31-7-2019	8-2-2022

and how i would like to show it:

So in a year filter with all the years (or relevant years) from my datetable:

ID	To appear in year filter
A	2015, 2016, 2017, 2018
B	2017… (and all years in datetable)
C	2019, 2020, 2021, 2022

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I'd like to suggest you to add a calendar table(no relationship to current table) as source of slicer and write a measure formula to filter and concatenate and display all matched years.

Measure =
VAR selected =
    MAX ( 'calendar'[Date] )
VAR _calendar =
    CALCULATETABLE (
        CALENDAR (
            MIN ( 'Table'[Start] ),
            IF (
                MAX ( 'Table'[End] ) <> BLANK (),
                MAX ( 'Table'[End] ),
                MAXX ( ALLSELECTED ( 'Table'[End] ), [End] )
            )
        ),
        VALUES ( 'Table'[ID] )
    )
RETURN
    CONCATENATEX (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( _calendar, [Date] >= selected ),
                "Year", YEAR ( [Date] )
            )
        ),
        [Year],
        ","
    )

Regards,

Xiaoxin Sheng