cancel
Showing results for
Did you mean:
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

## 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

## Re: Grouping by consecutive dates into date ranges

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

## Re: Grouping by consecutive dates into date ranges

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

Highlighted
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] )
)```
Frequent Visitor

## Re: Grouping by consecutive dates into date ranges

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

@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" )
)```

Frequent Visitor

## Re: Grouping by consecutive dates into date ranges

Thank you @Zubair_Muhammad, it almost works

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

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!

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!