Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Surya9
Helper V
Helper V

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 

Surya9_0-1637850538757.png

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

Surya9_1-1637850569233.png

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Surya9 

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:

vangzhengmsft_0-1638170577551.png

 

 

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.

View solution in original post

2 REPLIES 2
v-angzheng-msft
Community Support
Community Support

Hi, @Surya9 

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:

vangzhengmsft_0-1638170577551.png

 

 

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
Super User
Super User

@Surya9  can you try this measure

Measure = if(max(tbl[Range Start])>=min('Date'[Date])&&min(tbl[Range End])<=max('Date'[Date]),1,0)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.