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") 
)
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
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") 
)
Mooihoek's avatar
Mooihoek
Icon for Helper II rankHelper II
9 years ago

 

Doesnt seem to work,  I cant see why its not easy just to change the current Q1-4 date range to suit whatever you'd like?