Date Slicer - Data has project start and project end dates

[heidtmer]

Hello! I have a dataset that has project start and end dates, but want the date slicer to still include relative data if it falls within that date range. 

For example, if I filter on the year 2023. I want to be able to see projects that may have started in 2021 but do not have an end date until 2024.... I tried creating a date table and the following InDateRange measure, and then applying the measure = 1 to the visual but it is still not wokring the way i need it to... 

InDateRange =

var _rangeStart=FIRSTDATE('Date'[Date])

var _rangeEnd=LASTDATE('Date'[Date])

return

if(

SELECTEDVALUE(RoadMap[Start])>=_rangeStart

&&

SELECTEDVALUE(RoadMap[End])<=_rangeEnd

&&

SELECTEDVALUE(RoadMap[End])<>BLANK(),

1,0)

[Anonymous · Accepted solution]

Hi  @heidtmer ,

Please have a try.

Create a calendar table.

Table = CALENDAR(DATE(2020,1,1),DATE(2025,12,31))

Then put the date column into the slicer.

Then create measures.

 _start = YEAR(SELECTEDVALUE('Table'[Date]))-2

year_endrangeend = YEAR(SELECTEDVALUE('Table'[Date]))+1

max_start = MAXX(ALLSELECTED(Sheet7),Sheet7[_start])

max_end = MAXX(ALLSELECTED(Sheet7),Sheet7[year_endrangeend])

Measure = IF(year(MAX(Sheet7[date start]))>=[max_start]&&year(MAX(Sheet7[date end]))<=[max_end],1,BLANK())

Then put the measure into the visual.

If I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly