none
DAX measure to count distinct dates between date ranges over multiple rows RRS feed

  • Question

  • Hi everyone,

     

    I need to create a DAX measure that counts distinct dates between start and end date ranges across multiple rows. I didn't think it would be very difficult, but everything I've tried to piece together has failed.

     

    I have a trips table setup like this:

    Traveler_ID

    Trip_ID

    Start_Date

    End_Date

    Trip_Days

    52

    1

    04/15/19

    04/30/19

    16

    52

    2

    05/01/19

    05/03/19

    3

    52

    3

    05/01/19

    05/02/19

    2

    64

    8

    06/15/19

    06/30/19

    16

    67

    8

    06/10/19

    06/21/19

    12

    67

    9

    06/16/19

    06/29/19

    14

    89

    5

    05/27/19

    06/23/19

    28

    89

    6

    06/04/19

    06/29/19

    26

    98

    3

    05/15/19

    05/17/19

    3

    98

    7

    06/07/19

    06/22/19

    16

     

    Aggregating Trip_Days from the database would be incorrect since some date ranges overlap.

     

    This is what I would expect to see looking at distinct dates by Traveler:

    Traveler_ID

    Trip_Days

    52

    19

    64

    16

    67

    20

    89

    34

    98

    19

     

    & distinct dates by Trip:

    Trip_ID

    Trip_Days

    1

    16

    2

    3

    3

    5

    5

    28

    6

    26

    7

    16

    8

    21

    9

    14

     

    Any ideas on how to set this up? It would be much appreciated. Thanks!

     

    Thursday, January 23, 2020 4:49 PM

All replies

  • This is actually a really tricky problem. You have to expand out all the date ranges then group them and do a count of the distinct days.

    The following DAX appears to work

    =
    COUNTROWS(SUMMARIZE(GENERATE('Table',
    var _start = 'Table'[Start_Date]
    var _end = 'Table'[End_Date]
    return DATESBETWEEN('Date'[Date], _start, _end)), 'Date'[Date])
    )

    But I'm not sure what the performance would be like on a larger data set. Another option might to expand out the dates so rather than having the start/end columns you have a single date column and 1 row for every date between the start and end, then you could just do a DISTINCTCOUNT of the date column


    http://darren.gosbell.com - please mark correct answers

    Thursday, January 23, 2020 11:23 PM
    Moderator
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Count unique days of contiguous overlapping date ranges.
    Prepare data for PP with PQ.
    Two ways shown.
    http://www.mediafire.com/file/rmjjn10gf58io49/01_23_20a.xlsx/file
    http://www.mediafire.com/file/511aslps87lpbbt/01_23_20a.pdf/file

    Friday, January 24, 2020 5:24 AM