Forum Discussion
Consolidate Continuous Date Ranges into one record
First, very nicely done with presenting the full picture of your data in a form that can be easily copy and pasted as well as the desired result. Seems like you could potentially use the technique described here...
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
...to retun the MAX date of "First DOS" that is less than the "First DOS" of the current row (EARLIER) and then check if the difference between "Last DOS" of the current row minus that is 1. That would indicate that it is part of a continuous series. Obviously you would also have constraints for First and Last. Once you had that, you could potentially create a measure that did want you wanted but I haven't thought it that far through. If I have some time tonight I will look into this a little more.
Here is the formula which I was speaking about:
Column = VAR mydate = MAXX(FILTER(ALL(Problems),Problems[First DOS]<EARLIER(Problems[First DOS]) && Problems[First]=EARLIER(Problems[First]) && Problems[Last]=EARLIER(Problems[Last])),Problems[Last DOS]) RETURN IF(Problems[First DOS]-mydate = 1,1,0)