Display top hierarchy of calendar period based on user selection

[scrawfor]

I have a slicer that is organized by Year - > Month Name. I want to a card to display the most convenient/efficient grouping of months. But I'm looking to see if there's a simple way using ISINSCOPE or some other functions to make it "smart". For example, I only want it to display Quarter if complete quarters are selected and return the range in Mmm YYYY format if an "irregular" period is selected. Also, looking to test if the range selected is continuous. If there's a break in selection then display Concatenation of Mmm-YYYY selected periods. See some examples of slicer selections below.

I have a date calendar with all the hierarchies and am trying to avoid complicated IF/SWITCH expressions to check for complete hierarchies.

Card Output Example Table:

Should Display: "Q1 2022"	Should Display: "H1 2022"	Should Display: "Q4 2021 - Q1 2022"	Should Display: "Feb 2022 - May 2022"

I hope that makes sense. Thanks in advance for considering!

[scrawfor]

Awesome, thanks. It works but I was wondering if theres a simpler way to implement? Like working with PATH functions? Maybe the better way to ask the question is, is there a way to display the parent entity if all child entities are selected in a slicer? Hierarchies can get more complicated and with many levels. IF statements become huge as the levels in a hierarchy grows.

[v-yueyunzh-msft · Accepted solution]

Hi, @scrawfor 

According to your descriptionAccording to your description, you want to select different years and months according to the slicer to judge, and display different values on the card according to different situations.. Right?

Here are the steps you can follow：

(1) We can use the Calendar() function to generate a date table, and we can add the desired dimension to judge:

Date = ADDCOLUMNS( CALENDAR( DATE(2020,1,1),DATE(2022,12,31)),

"Year", YEAR ( [Date] ),

"Quarter", ROUNDUP(MONTH([Date])/3,0),

"year_Q", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0),

"year_H", "H" & ROUNDUP(MONTH([Date])/6,0)&" " &YEAR([Date]) ,

"yearmonth", year([Date]) * 100 + MONTH([Date]),

"yearmonth2",YEAR([Date]) &FORMAT( [Date],"mmm")

)

(2)We can ceate a measure : “test_card”

test_card =
VAR _slice_YearQ =
    VALUES ( 'Date'[year_Q] )
VAR _slice_Year =
    VALUES ( 'Date'[Year] )
VAR _slice_Q =
    VALUES ( 'Date'[Quarter] )
VAR _slice_date =
    VALUES ( 'Date'[Date] )
VAR _slice_YearMonth =
    VALUES ( 'Date'[yearmonth] )
VAR _slice_YearH =
    VALUES ( 'Date'[year_H] )
VAR _max_date =
    MAXX ( _slice_date, [Date] )
VAR _min_date =
    MINX ( _slice_date, [Date] )
VAR _slice_count_Q =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Date'[Date], 'Date'[year_Q] ),
                'Date'[Date] > _min_date
                    && 'Date'[Date] < _max_date
            ),
            "YearQ", [year_Q]
        )
    )
VAR _slice_count_year_month =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Date'[Date], 'Date'[yearmonth] ),
                'Date'[Date] > _min_date
                    && 'Date'[Date] < _max_date
            ),
            "yearmonth", [yearmonth]
        )
    )
VAR _slice_yearmonthe2 =
    VALUES ( 'Date'[yearmonth2] )
RETURN
    IF (
        COUNTROWS ( _slice_YearQ ) = 1
            && COUNTROWS ( _slice_YearMonth ) = 3,
        "Q" & _slice_Q & "   " & _slice_Year,
        IF (
            COUNTROWS ( _slice_YearH ) = 1
                && COUNTROWS ( _slice_YearMonth ) = 6,
            _slice_YearH,
            IF (
                COUNTROWS ( _slice_YearMonth )
                    = 3 * COUNTROWS ( _slice_count_Q ),
                "Q"
                    & ROUNDUP ( MONTH ( _min_date ) / 3, 0 ) & "  "
                    & YEAR ( _min_date ) & "-" & "Q"
                    & ROUNDUP ( MONTH ( _max_date ) / 3, 0 ) & "  "
                    & YEAR ( _max_date ),
                IF (
                    COUNTROWS ( _slice_count_year_month ) = 1,
                    FORMAT ( _min_date, "mmm" ) & "   "
                        & YEAR ( _min_date ),
                    IF (
                        COUNTROWS ( _slice_count_year_month ) = COUNTROWS ( _slice_YearMonth ),
                        FORMAT ( _min_date, "mmm" ) & "   "
                            & YEAR ( _min_date ) & "-"
                            & FORMAT ( _max_date, "mmm" ) & "   "
                            & YEAR ( _max_date ),
                        CONCATENATEX ( _slice_yearmonthe2, [yearmonth2], "," )
                    )
                )
            )
        )
    )

(3)We can put the [Date] in the slice and put the [test_card] in the card, and then we can meet your need:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly