create a dynamic title based on slicer of date hierarchy capturing all year, quarter, and month

[Anonymous]

Goal: I have a date slicer of date hierarchy (year, quarter, month). Based on user's selection, I need to create a dynamic title that captures all year, quarter, and month selected. Quarter(s) and month(s) follow after their respective year. For example, 2023 Q1 M1, 2 & 2024 Q2 M4. The title can get long but I will keep the question simple for now and worry about the length later. 

Data Model: One fact table and one calendar table related by the [Date] column. Calendar table was generated from the [Date] column in the fact table. The calendar table has following 4 columns: [Date] - date type,  [Year] & [Quarter] & [Month] - whole number type. 

This question is about the date slicer selection. I will save the time by not involving the fact table here. 

DAX I need help with: 
Title = 

VAR SelectedYears =

    ADDCOLUMNS(

        VALUES('Calendar'[Year]),

        "SelectedQuarters",

        CONCATENATEX(

            FILTER(

                VALUES('Calendar'[Quarter]),

            'Calendar'[Year] = EARLIER('Calendar'[Year])

            ),

            [Quarter],

            ", "

            ),

        "SelectedMonths",

        CONCATENATEX(

            FILTER(

                VALUES('Calendar'[Month]),

                'Calendar'[Year] = EARLIER('Calendar'[Year])),

                [Month],

                ", ", 'Calendar'[Month], ASC

            )

    )

RETURN

    CONCATENATEX(

        SelectedYears,

        'Calendar'[Year] & " Q" &

         [SelectedQuarters] & " M"

         & [SelectedMonths],

        " & "

    )

Problem of this DAX: 

Instead of the desired output (for example: 2023 Q1 M1, 2 & 2024 Q2 M4), the above DAX returns all quarters and months after each year selected: 2023 Q1, 2 M1, 2, 4 & 2024 Q1, 2 M1, 2, 4. 

What is the correct DAX to achieve my goal? Please help. Thank you! 

[Anonymous]

@lbendlin You are such a genius! Your DAX is simple and works wonder. I could have stopped here. But I added a few lines on top of your DAX to produce the right format for my situation - reduced the title length just a little bit.

Title =

VAR SelectedYears =

SELECTCOLUMNS(

    SUMMARIZE(

        'Calendar',

        'Calendar'[Year],

        "Combined_Quarter",

        CONCATENATEX(

            VALUES('Calendar'[Quarter]),

            'Calendar'[Quarter],

            ", ", 'Calendar'[Quarter], ASC

        ),

        "Combined_Month",

        CONCATENATEX(

            VALUES('Calendar'[Month]),

            'Calendar'[Month],

            ", ", 'Calendar'[Month],ASC

        )

    ),

    "Year", [Year],

    "Quarter", [Combined_Quarter],

    "Month", [Combined_Month]

)

Return

CONCATENATEX(

    SelectedYears,

    [Year] & " Q" & [Quarter]

     & " M" & [Month], " & ", [Year], ASC

)

My tips/takeaway from this experience is, in my original DAX, the EARLIER function does not work at all in this context. And SUMMARIZECOLUMNS is the right DAX to use.  

Thank you @lbendlin! 

[lbendlin · Accepted solution]

Title = 

VAR SelectedYears =
    SUMMARIZECOLUMNS(
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month]
)
RETURN
    CONCATENATEX(
        SelectedYears,
        [Year] & " Q" & [Quarter] & " M" & [Month],
        " & "
    )