Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 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!

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

 

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?

 

 

Capture.PNG

Anonymous
Not applicable

 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
Not applicable

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
Microsoft Employee
Microsoft Employee

 

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

 

7.PNG

 

8.PNG

 

Regards,

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.