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

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
    )