Displaying a list of selected months

Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:

I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:

Sel = 
VAR Months =
        VALUES ( 'Date'[Month Number] )
    VAR MonthsPS =
        ADDCOLUMNS (
            Months,
            "PrevSel", 'Date'[Month Number] - 1 IN Months,
            "NextSel", 'Date'[Month Number] + 1 IN Months
        )
    VAR MonthsPSS =
        ADDCOLUMNS (
            MonthsPS,
            "Seq", SWITCH (
                TRUE (),
                [PrevSel] && [NextSel], "Inside",
                [NextSel], "First",
                [PrevSel], "Last",
                "Single"
            )
        )
    VAR MonthsPSSNoInside =
        FILTER ( MonthsPSS, [Seq] <> "Inside" )
    VAR MonthsRanked =
        ADDCOLUMNS (
            MonthsPSSNoInside,
            "ValPos", RANKX ( MonthsPSSNoInside, [Month Number], , ASC )
        )
    VAR MonthsWithNext =
        ADDCOLUMNS (
            MonthsRanked,
            "NextMonth", 
            MAXX (
                FILTER ( MonthsRanked, [ValPos] = EARLIER ( [ValPos] ) + 1 ),
                [Month Number]
            )
        )
    VAR ValuesToShow =
        FILTER (
            ADDCOLUMNS (
                MonthsWithNext,
                "ValueToShow", SWITCH (
                    [Seq],
                    "Single", LOOKUPVALUE ( 'Date'[Month], [Month Number], [Month Number] ),
                    "First", LOOKUPVALUE ( 'Date'[Month], [Month Number], [Month Number] ) & "-"
                        & LOOKUPVALUE ( 'Date'[Month], [Month Number], [NextMonth] )
                )
            ),
            [ValueToShow] <> ""
        )
    VAR Result =
        CONCATENATEX ( ValuesToShow, [ValueToShow], ", ", [Month Number] )
    RETURN
        Result

If you want to play with this code download the pbix file.

The web is a beautiful place! It didn’t take more than half a day, that Chris Webb proposed a much better solution in the comments (thanks Chris!), he provided the idea, I cleaned it a bit, and this new version looks way shorter, more readable and much more efficient. Because I know many people don’t read the comments, here is the new code:

Sel (much better version) = 
VAR SelectedMonthNumbers =
    VALUES ( 'Date'[Month Number] )
VAR MonthsAndNames =
    SUMMARIZE ( 'Date', 'Date'[Month Number], 'Date'[Month] )
VAR Result = 
    CONCATENATEX (
        MonthsAndNames,
        VAR CurrentMonthNumber = 'Date'[Month Number]
        VAR CurrentMonthName = 'Date'[Month]
        VAR IsNextSelected = CurrentMonthNumber + 1 IN SelectedMonthNumbers
        VAR IsPrevSelected = CurrentMonthNumber - 1 IN SelectedMonthNumbers
        RETURN
            IF (
                NOT ( IsPrevSelected && IsNextSelected ),
                CurrentMonthName & IF ( IsNextSelected, "-", ", " )
            ),
        "",
        'Date'[Month Number]
    )
RETURN 
    LEFT ( Result, LEN ( Result ) - 2 )