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") 
)
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.