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 ,
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