cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lkarolak Frequent Visitor
Frequent Visitor

Grouping by consecutive dates into date ranges

This is my current data table structure:

 

Date            Name

---------------------

01.03.2018  Mark

02.03.2018  Mark

03.03.2018  Mark

07.03.2018  John

08.03.2018  John

15.03.2018  Steve

 

 

What I would like to achieve is kind of grouping by consecutive dates, so that I have at the end something like this:

 

Date from     Date Until    Name

------------------------------------

01.03.2018    03.03.2018   Mark

07.03.2018    08.03.2018   John

15.03.2018    15.03.2018   Steve

 

Any tips? Thank you in advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Grouping by consecutive dates into date ranges

@lkarolak

 

Essentially I have added 3 calculated columns to identify the boundaries of consective dates which can then be used for Groupings

 

SeriesBoundaries =
VAR PriorName =
    CALCULATE (
        VALUES ( TableName[Name] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[SeriesStart]
                = EARLIER ( TableName[SeriesStart] ) - 1
        )
    )
VAR NextName =
    CALCULATE (
        VALUES ( TableName[Name] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[SeriesStart]
                = EARLIER ( TableName[SeriesStart] ) + 1
        )
    )
RETURN
    IF (
        PriorName <> TableName[Name],
        "Series Start",
        IF ( NextName <> TableName[Name], "Series End" )
    )

 

Super User
Super User

Re: Grouping by consecutive dates into date ranges

HI @lkarolak

 

Please change the formua of Series Start as follows

 

SeriesStart =
VAR PreviousDate =
    CALCULATE (
        MAX ( TableName[Date ] ),
        FILTER ( TableName, TableName[Date ] < EARLIER ( TableName[Date ] ) )
    )
VAR PreviousName =
    CALCULATE (
        FIRSTNONBLANK ( TableName[Name], 1 ),
        FILTER ( TableName, TableName[Date ] = PreviousDate )
    )
VAR myrank =
    RANKX ( TableName, TableName[Date ],, ASC, DENSE )
RETURN
    IF (
        PreviousDate
            <> TableName[Date ] - 1
            && TableName[Name] = PreviousName,
        myrank + 1,
        myrank
    )
13 REPLIES 13
Super User
Super User

Re: Grouping by consecutive dates into date ranges

HI @lkarolak

 

You can use a Table Visual..

 

Place the Name Field in values. Drag the Date Field in the Values Section twice and choose the earliest and latest aggreagtions

 

Grouping.png

 

Highlighted
Super User
Super User

Re: Grouping by consecutive dates into date ranges

@lkarolak

 

Or you can create a calculated Table

 

from the Modelling Tab>>New Table

 

Table =
SUMMARIZE (
    TableName,
    TableName[Name],
    "Date From", MIN ( TableName[Date] ),
    "Date Until", MAX ( TableName[Date] )
)
lkarolak Frequent Visitor
Frequent Visitor

Re: Grouping by consecutive dates into date ranges

Thank you @Zubair_Muhammad

 

This approach with SUMMARIZE is fine, but when there are more than one ranges for a name, then it is not working correctly. I mean, if I have:

 

Date            Name

---------------------

01.03.2018  Mark

02.03.2018  Mark

03.03.2018  Mark

07.03.2018  John

08.03.2018  John

15.03.2018  Steve

20.04.2018  Mark

21.04.2018  Mark

22.04.2018  Mark

 

Then the result for "Mark" would be :

 

Date from     Date Until    Name

------------------------------------

01.03.2018    22.04.2018   Mark

 

Which is wrong for my scenario.

I would need something like this:

 

Date from     Date Until    Name

------------------------------------

01.03.2018    03.03.2018   Mark

07.03.2018    08.03.2018   John

15.03.2018    15.03.2018   Steve

20.04.2018    22.04.2018   Mark

 

Thank you!

Super User
Super User

Re: Grouping by consecutive dates into date ranges

Super User
Super User

Re: Grouping by consecutive dates into date ranges

@lkarolak

 

Essentially I have added 3 calculated columns to identify the boundaries of consective dates which can then be used for Groupings

 

SeriesBoundaries =
VAR PriorName =
    CALCULATE (
        VALUES ( TableName[Name] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[SeriesStart]
                = EARLIER ( TableName[SeriesStart] ) - 1
        )
    )
VAR NextName =
    CALCULATE (
        VALUES ( TableName[Name] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[SeriesStart]
                = EARLIER ( TableName[SeriesStart] ) + 1
        )
    )
RETURN
    IF (
        PriorName <> TableName[Name],
        "Series Start",
        IF ( NextName <> TableName[Name], "Series End" )
    )

 

lkarolak Frequent Visitor
Frequent Visitor

Re: Grouping by consecutive dates into date ranges

Thank you @Zubair_Muhammad, it almost works Smiley Happy

 

This is the actual initial data that I have:

 

But then I get this after using the approach you suggested. The first row is exactly what I want, however, the "single" ones are still listed: 

 

What am I doing wrong?

Thanks!

Super User
Super User

Re: Grouping by consecutive dates into date ranges

Hi @lkarolak

 

Could you share your file?

 

 

lkarolak Frequent Visitor
Frequent Visitor

Re: Grouping by consecutive dates into date ranges

@Zubair_Muhammad My mistake, forgot to set the filter on visual level to match the "SeriesStart".

 

It's working good now, thanks a lot for your support!

lkarolak Frequent Visitor
Frequent Visitor

Re: Grouping by consecutive dates into date ranges

@Zubair_Muhammad Sorry, actually there is something missing still.

 

If we have this example:

 

Date            Name

----------------------

01.04.2018  Claudia

03.04.2018  Claudia

 

Then this is treated as one range (01.04.2018-03.04.2018), although it should be two separate 1 day ranges.

 

See the example file here: click

 

Thank you!