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

 

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!

Highlighted
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!