Date row assistance

[ChristoAClark]

I have a list of dates for eligibility in row format. I am trying to ignore the end date if there is no gap between the start (eligible) and end date. For example, I want to show 1/04/2011 - 6/26/2011. This would be a better "at a glance" for the user. I have tried a number of things, but can't seem to wrap my head around what I am missing. Any help would be appreciated. 

[Anonymous · Accepted solution]

Hi @ChristoAClark ,

Here is my sample data:
start dataend data

3/12/2008	3/11/2009
1/4/2011	3/1/2011
3/2/2011	6/21/2011
6/22/2011	6/26/2011
7/25/2011	2/20/2012
2/8/2013	7/26/2013
7/27/2013	8/3/2013
7/1/2024	9/8/2024
9/9/2024	11/1/2024

Add an Index column first:

Index = RANKX(ALL('table'), 'table'[start date], ,ASC, Dense)

Then add a column as a Tag:

Tag = var a=[end date]+1
var b=[Index]
var c=LOOKUPVALUE('table'[start date],'table'[Index],[Index]+1)
return IF(a=c,1,0)

Use this DAX to create a calculated column for end date:

EndDate_range = IF([Tag]=0,[end date],MINX(FILTER('table',[Tag]=0&&[Index]>EARLIER('table'[Index])),[end date]))

And use this DAX to create a calculated column for start date:

StartDate_range = MINX(FILTER('table',[EndDate_range]=EARLIER('table'[EndDate_range])),[start date])

The final output is as below:

In the table visual:

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

[ChristoAClark · Accepted solution]

Holy cow! You are my new hero. THis works exactly as I hoped. Thanks so much. 

[Anonymous · Accepted solution]

Hi @ChristoAClark ,

Here is my sample data:
start dataend data

3/12/2008	3/11/2009
1/4/2011	3/1/2011
3/2/2011	6/21/2011
6/22/2011	6/26/2011
7/25/2011	2/20/2012
2/8/2013	7/26/2013
7/27/2013	8/3/2013
7/1/2024	9/8/2024
9/9/2024	11/1/2024

Add an Index column first:

Index = RANKX(ALL('table'), 'table'[start date], ,ASC, Dense)

Then add a column as a Tag:

Tag = var a=[end date]+1
var b=[Index]
var c=LOOKUPVALUE('table'[start date],'table'[Index],[Index]+1)
return IF(a=c,1,0)

Use this DAX to create a calculated column for end date:

EndDate_range = IF([Tag]=0,[end date],MINX(FILTER('table',[Tag]=0&&[Index]>EARLIER('table'[Index])),[end date]))

And use this DAX to create a calculated column for start date:

StartDate_range = MINX(FILTER('table',[EndDate_range]=EARLIER('table'[EndDate_range])),[start date])

The final output is as below:

In the table visual:

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