Grouping by consecutive dates into date ranges

[lkarolak]

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!

[Zubair_Muhammad · Accepted solution]

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

[Rune]

Hi,

Would anyone be able to help me with an extension of this solution? I would like to group by consecutive date ranges as previous example but I would like the dates to be grouped even though there is a weekend/holiday within the date range. 

So if the dates are:
Friday, 01 March 2018
Monday, 04 March 2018
Tuesday, 05 March 2018

They should still be grouped as consecutive.
Many thanks in advance

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@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]

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!

[Zubair_Muhammad]

HI @lkarolak

Please see the attached File here

[Zubair_Muhammad · Accepted solution]

@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]

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!

[Zubair_Muhammad]

Hi @lkarolak

Could you share your file?

[lkarolak]

@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]

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

[Zubair_Muhammad · Accepted solution]

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
    )

[lkarolak]

Thank you @Zubair_Muhammad, that was it!!

[Zubair_Muhammad]

@lkarolak

Please see the revised file here

[srikanth_pendem]

Hi @Zubair_Muhammad ,

I am not able to get that lastDate part. Could you please share the file?

thanks,

Srikanth

[Anonymous]

Hi @Zubair_Muhammad,

I have a very similar problem with one critical difference, my source data can have multiple transaction on the same date. See below:

Original RequirementMy Requirement

In my requirement, I want to ignore the second transaction for Mark on the 1st March 2018. This is the result I need:

Date	Name	SeriesStart	SeriesBoundaries	Last Date
Thursday, 01 March 2018	Mark	1	Series Start	Thursday, 01 March 2018
Thursday, 01 March 2018	Mark	1
Saturday, 03 March 2018	Mark	3	Series Start	Saturday, 03 March 2018
Wednesday, 07 March 2018	John	3	Series Start	Thursday, 08 March 2018
Thursday, 08 March 2018	John	4	Series End
Thursday, 15 March 2018	Steve	5	Series Start	Thursday, 15 March 2018
Tuesday, 20 March 2018	Mark	6	Series Start	Thursday, 22 March 2018
Wednesday, 21 March 2018	Mark	7
Thursday, 22 March 2018	Mark	8	Series End
Sunday, 01 April 2018	Claudia	9	Series Start	Sunday, 01 April 2018
Tuesday, 03 April 2018	Claudia	11	Series Start	Tuesday, 03 April 2018

How would you change your code to handle this situation? I have re-uploaded your solution with my requirement here:

My Requirement

Thanks