Reply
Member
Posts: 70
Registered: ‎07-14-2018
Accepted Solution

Year and month Hierarchy

[ Edited ]

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

.


Accepted Solutions
Community Support Team
Posts: 4,028
Registered: ‎07-09-2016

Re: Year and month Hierarchy

@Samrawit21,

 

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" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Year and month Hierarchy

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

2018-09-14_17-02-03.png

 

 

 

View solution in original post


All Replies
Community Support Team
Posts: 4,028
Registered: ‎07-09-2016

Re: Year and month Hierarchy

@Samrawit21,

 

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" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 70
Registered: ‎07-14-2018

Re: Year and month Hierarchy

[ Edited ]

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?

Skärmklipp.PNG

 

Member
Posts: 70
Registered: ‎07-14-2018

Re: Year and month Hierarchy

[ Edited ]

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!

Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Year and month Hierarchy

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

2018-09-14_17-02-03.png

 

 

 

Member
Posts: 70
Registered: ‎07-14-2018

Re: Year and month Hierarchy

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

Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Year and month Hierarchy

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

2018-09-14_17-33-04.png

 

 

 

Member
Posts: 70
Registered: ‎07-14-2018

Re: Year and month Hierarchy

I recreate another hierarchy slicer and it works fine!

Thanks!