Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

 

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  can you try this measure

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