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?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     Hi 

    I tried using as below and it works for me.  My financial year starts from April

     

     "FYQTR", SWITCH(MONTH([Date]),
    1,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q4",2,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q4",3,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q4",
    4,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q1",5,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q1",6,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q1",
    7,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q2",8,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q2",9,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q2",
    10,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q3",11,"FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q3","FY"&Right(Format(If( Month([Date]) >= 4 , Year([Date])+1,Year([Date]) ),"0#"),2)&"Q3")

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      The real issue is that once I define a calendar and custom date hierarchy, it doesn't recognize it as a time-based series.  So Power Bi forces you to make a choice:  Do you want quarter to be based on calendar year to be able to use time-based visuals, or do you want fiscal quarter and make do with other visual options?

       

      That is, default time hierarchy = continuous and custom hierarchy = categorical.