Forum Discussion

ChristoAClark's avatar
ChristoAClark
Frequent Visitor
2 years ago
Solved

Date row assistance

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/201...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    ChristoAClark
    2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.