Calendar Year vs Fiscal Year Clustered Column Charts

[bdehning]

Basic Question.

My main database tables have data that is not based on 01/01/years.  

Why then,  when I create Year Hierarchy and use Year and Month for X-Axis that every company chart I pull up all show January - December and do not start with their particular starting month of the year, which could be any of the 12 months.  

What do I not understand?

[pmreis]

Hi @bdehning 

In order for other users to help you more quickly and assertively, it's important to provide the problem with all the possible details. I recommend to take a look at this post on how you maybe attach a diagram of your data model, or maybe even a PBIx sample.  

How to Get Your Question Answered Quickly 

[pmreis]

Hi @bdehning 

The behavior you're observing is due to the default behavior of Power BI when creating a Year-Month hierarchy. By default, Power BI assumes a calendar year, which starts in January and ends in December. Even if your data starts in a month other than January, the hierarchy will still begin with January.

Here's what's happening:

1. Default Calendar Behavior: When you create a Year-Month hierarchy, Power BI is using a standard calendar for the hierarchy. This means it will always start with January and end with December, regardless of the data in your table.

2. Data Display: Even if your data for a particular company starts in, say, March, the hierarchy will still show January and February, but there will be no data plotted for those months.

 

How to Address This:

1. Custom Calendar Table: Create a custom calendar table that starts with the specific month of your company's fiscal year. This way, you can have a fiscal year that starts in any month you choose. Once you have this table, create relationships between your data table and the custom calendar table. Use the dates from this table for your visualizations.

2. Sort by Column: If you adjust the month names to reflect your fiscal year (e.g., "Fiscal Month 1", "Fiscal Month 2", etc.), you can then use the "Sort by Column" feature in Power BI to ensure they appear in the correct order in your visuals.

3. Avoid Default Hierarchies: Instead of using the default Year-Month hierarchy, you can create two separate columns for Year and Month. Then, use these columns in your visuals. This gives you more control over how data is displayed.

By understanding that Power BI's default behavior is to use a standard calendar year and by implementing one of the solutions above, you can have your visuals display data starting from the specific month that aligns with your company's fiscal year.

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

[bdehning]

Good explanation but my table is not for just one company.   Charts are used for companies that have Fiscal Years that can be any month between Jan and Dec.  

How can that be accounted for?