Forum Discussion

lkarolak's avatar
lkarolak
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    lkarolak
    Frequent Visitor
    8 years ago

    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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

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