How do you define QTR in Date Hierarchy?

[Anonymous]

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 overly complicated.  At least my approach is...

I created a mapping table with Month (name), Day, Year, QTR, and full date.  Made a hierarchy out of Month, Day, Year, QTR and created a relationship between the date in the data and full date.  It works...  but the behaviour is completely different than the default hierarchy.

For example, ordering the records by date will order month alphabetically.  So now I have to define a cutom ordering scheme in the mapping file.  And on a graph, the axis markers display the full hierarchy name and it's way too long.  Moreover, it doesn't recognize the hierarchy as an actual date - it's "categorical" in nature.

All of this just to define the qtr seems overkill.  Am I missing something?

Thanks in advance!

[Phil_Seamark · Accepted solution]

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]

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]

 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]

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.

[v-sihou-msft]

@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,