Forum Discussion
Custom Date table based on date field in FACT table with hierarchy applied to datetime field
Hi Riyaz999 ,
When you use the full Date column as a continuous axis in Power BI, it creates a single chronological timeline, resulting in one long line across all years rather than overlapping lines. This is the expected behavior. If you want to compare multiple years on the same January - December scale, you will need a normalized axis like DayOfYear or a Month-Day index. This method aligns all years onto one seasonal timeline and allows for separate lines for each year. A continuous date axis is not designed to overlay years on top of each other.
Hi Riyaz999 ,
Good observation on Issue 2, the month sorting adjustment is definitely the right approach. Regarding the zoom issue, this happens because the visual uses the date hierarchy and only considers the current data range, which restricts the default view options. Try switching the X-axis to the flat Date column from your Date table, set the axis type to Continuous, and manually set the Start date to Jan 1 and End date to Dec 31 (or your full range). This will ensure the chart displays the entire period by default, and the zoom slider will let you focus on specific details instead of just scrolling horizontally.
Hello v-sshirivolu,
The chart I am trying to display looks like:
where there is a single line for each year and the x-axis shows the day and month values (omitting the year).
Although the approach you had suggested does give me more control that I am not seeing using the date hierarchy, I no longer have one year of calendar dates along the x-axis but three years. Separate dates for January 1, 2023 through January 26, 2026. i.e., similar to that shown below:
I tried creating a string representation of the date, i.e., May 21, but it didn't let me sort that field by date. Since I am using direct-query mode, I used the following T-SQL to create that custom format date:
FORMAT([Date], 'MMM-dd') AS FormattedDate.
Unfortunately, PowerBI doesn't allow me to sort by date for my FormattedDate field.
Is there a way to display the data in continuous fashion without losing the depiction of one data series for each year?
Regards,
Riyaz
Hi Riyaz999 ,
You can achieve this without using the full Date on the axis. Create a DayOfYear (1–365/366) column in your Date table and use that field on the X-axis set to Continuous, then place Year in the Legend. This allows the visual to display a single Jan–Dec timeline while plotting a separate line for each year in the correct date order.Hi Riyaz999 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Hi Riyaz999 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions