Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
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.
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:
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.
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.
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.
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.
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.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |