Year and month Hierarchy

[Anonymous]

I want to create a year and month hierarchy in the following order
Year

      Tertial 1

         January

        February

        March

        April

     Tertial 2

             May

             June

             July

             August

      Tertial 3

            September 

            october

            November

            December

How can I create this kind of Hierarchy?

I couldn't manage to put the tertial names under the year and put the months below.

All the Tertials are under year column and each Tertial contains months

.

[v-chuncz-msft · Accepted solution]

@Anonymous,

Add the following calculated column and refer to this demo.

Tertial =
VAR m =
    MONTH ( 'Table'[Date] )
RETURN
    SWITCH ( TRUE (), m <= 4, "Tertial 1", m <= 8, "Tertial 2", "Tertial 3" )

[v-chuncz-msft · Accepted solution]

@Anonymous,

Add the following calculated column and refer to this demo.

Tertial =
VAR m =
    MONTH ( 'Table'[Date] )
RETURN
    SWITCH ( TRUE (), m <= 4, "Tertial 1", m <= 8, "Tertial 2", "Tertial 3" )

[Anonymous]

Thank you for your reply!

I want to have a hierarchy like an image below, so I created another calculated column using the following formula."Month = IF('table'[Tertial]="tertial 1";[Datum].[Month];IF('table'[Tertial]= "tertial 2";[Datum].[Month];[Datum].[Month]))" and it gave me the results like the image below, but the month names are not in the right order.how can I sort the month names?

[Anonymous · Accepted solution]

You can create a month number column and then use it to sort the your current Month.

[Anonymous]

I did that, but it still gives me the same results when I put it in the hierarchy.

[Anonymous]

Are you using Hierarchy Slicer visualization?  This can work well in my test env.

[Anonymous]

I recreate another hierarchy slicer and it works fine!

Thanks!

[Anonymous]

I used this formula to get month numbers from the tertial column so that I can get the months in the right order. Is there a way to format or change the month numbers to month name on this formula?

=SWITCH([Tertial];"Tertial 1";MONTH([Datum]);"Tertial 2";MONTH([Datum]);MONTH([Datum]))

Thanks in advance!