Dax Measure to filter table based on date range and two dates

[Anonymous]

Hi,

I am trying to create the measure which check the whether "Range start" and "Range end" is blank then output should be 1 or "Range start" and "Range end" is between date range of slicer then 0 else 1
below is the table 

all dates are in mm/dd/yyyy format 

if date slicer range is 11/25/2021 to 11/30/2021 Then expected output should be

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create a measure like this:

Measure = 
var _minDate=CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _maxDate=CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))

var _rangeStart=CALCULATE(MAX('Table'[Range start]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))
var _rangeEnd=CALCULATE(MAX('Table'[Range end]),FILTER(ALL('Table'),'Table'[Employeeid]=MAX('Table'[Employeeid])))

var _slicer=GENERATESERIES(_minDate,_maxDate)
var _t=GENERATESERIES(IF(_rangeStart<>BLANK(),_rangeStart,0),IF(_rangeEnd<>BLANK(),_rangeEnd,0))

var _count=COUNTROWS(_slicer)
var _except=COUNTROWS(EXCEPT(_slicer,_t))

return IF(_count<>_except,0,1)

Result:

Best Regards,
Community Support Team _ Zeon Zheng

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

[smpa01]

@Anonymous  can you try this measure

Measure = if(max(tbl[Range Start])>=min('Date'[Date])&&min(tbl[Range End])<=max('Date'[Date]),1,0)