Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How do you define QTR in Date Hierarchy?

Like a lot of companies, our fiscal year does not align with calendar year.  Is there no way to define the qtr in the default date hierarchy?  I know you can create a custom one, but it seems so over...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi Anonymous,

     

    It's well worth the effort to create your own Calendar table.  Just use the Create Table option from the modelling tab and paste this in.  You can see how easy it is to add dynamic columns to the table and customise to your needs.

     

    My Date Table= ADDCOLUMNS(
	CALENDARAUTO() ,
	"MonthID" , INT(FORMAT([Date],"YYYYMM")) ,
    "Month" , FORMAT([Date],"MMM YY"),
    "Quarter" , SWITCH(MONTH([Date]),
                1,"Q1",2,"Q1",3,"Q1",
                4,"Q2",6,"Q2",6,"Q2",
                7,"Q3",8,"Q4",9,"Q4",
                10,"Q4",11,"Q4","Q4") 
)
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

 

Anonymous

 

According to your description, you want to define the fiscal quarter in your fiscal calendar hierarchy. Right?

 

In this scenario, I assume you already have a Fiscal number of month and Fiscal year column in your table. Then you suppose to create the Fiscal Quarter based on the the fiscal number of month with Fiscal year concatenated.

 

To sort the records, you need to have a Month Number column instead of a Month Name column. Otherwise, it will sort alphabetically.

 

If you put entire hierarchy into a chart visual, it should display corresponding level in that chart area.

 

 

 

Regards,