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 ,
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
Hello there,
unfortunately, the solution of showing the days, 1 through 365, won't work for the users. They are okay with not showing the actual day of the month also. i.e., something similiar to:
which I assume is a continuous not categorical representation of the date. I can ensure that my FACT table would have a full three previous years of data plus anything in the current year up to today. I would want four lines on my graph, one for each of the previous years from January 1 through December 31 plus a single line from January 1 to today. By showing only a single calendar year on the x-axis, one could compare data from one year to the next and include a typical range in a shaded colour. I am open to restructuring data if required.
Thanks in advance
Hi Riyaz999 ,
You can solve this by using the actual Date column on the X-axis instead of DayOfYear, and setting the axis type to Continuous not a date hierarchy. Make sure your Date table is properly marked as a Date table and includes all the years you need. Then place Year in the Legend so each year appears as a separate line on the same January to December timeline. Previous years will show a full year, while the current year will automatically stop at today, making year over year comparison easy. If needed, you can also add a typical range by calculating the average or min/max of past years and showing it as a shaded area behind the lines. This keeps the chart simple, continuous, and consistent with the example you shared.Hi v-sshirivolu,
thanks for your prompt response. As per your suggestion, I have used the actual date from my date table on the x-axis:
I have also ensured that DateDim is marked as a date table:
but unfortunately, I get one long date line instead of 4 separate lines (one per year):Hopefully, you are able to spot something that I missed.
Regards,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 ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you