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/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. 

 

 

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. 

2 Replies

  • 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.

  • ChristoAClark's avatar
    ChristoAClark
    Frequent Visitor
    2 years ago

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