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.
Put your day value in x-axis and from the visual settings change type to "Categorical".
Hello again cengizhanarslan,
I had set the x-axis as categorical but it still didn't represent what I was hoping for. I made the following updates:
and now, it is starting to look like what I was hoping.
so thanks for all of your help that got me to this point. Here is a summary of the solution based on your helpful feedback:
1) Created the two separate dim tables in SQL based on FACT table, one for date and one for time
2) In the PowerBI Model View, each Dim table was linked directly to the fact table
3) A date hierarchy was created in the DimDate table that looks as follows:
4) The date hierarchy was dragged into the x-axis well but Year and Month were removed
5) The date hierarchy was dragged into the Legend well but all fields were removed with the exception of Year
6) Since I have hourly data, the Average value (for a given day) is represented in the y-axis well
Remaining issues:
1) Zoom level should be set such that the full graph is visible without a horizontal scrollbar. I would want a zoom slider that allows for showing more detail but the default should show the fill graph and I don't see where I can set this. I am also only seeing a slider on the y-axis which is likely due to the date hierarchy being used on the x-axis
2) My data seems to start from April 1st, where I would want the left most point to be January 1st. Not sure why the chart is defaulting to what appears to be a fiscal calendar???
Regards,
R
Issue 2) resolved. It was a sorting issue where it was sorting by month name in alphabetical order. Using the month number for sorting now. Can't believe that I missed that.
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