Forum Discussion
PBI Visualization
I have data
from 2024: April to Dec
from 2025: Jan to May,
so there is any overlap (additional data) for months of April and May, and when display those 2 month looked to have peaked and misleading.. what is the best way to show this kind of data? thanks.
Hi tan_thiamhuat ,
This is a great question and a pretty common challenge when visualizing overlapping time periods in Power BI. What’s happening in your graph is that April and May from two different years (2024 and 2025) are being plotted on the same axis, so their values are stacking up and creating artificial peaks.
Here’s what you can do to show this data more accurately:
Combine Year and Month in Your Axis:
Instead of just using "Month" as your x-axis, create a new column in your data for "Year-Month" (for example, 2024-04, 2024-05, 2025-04, 2025-05). This way, Power BI will treat each month from each year as a separate point, and you won’t get overlapping values.Use a Hierarchical Axis:
In Power BI, you can set up your axis as a hierarchy: Year > Month. This lets you expand or collapse the data to see trends by year and by month, without mixing the same months from different years.Color/Legend for Data Types:
Since you have "Cancelled Booking" and "New Booking", make sure these are shown in the legend as separate lines or bars, so it’s clear which series is which.Check for Duplicates:
Double-check your data to make sure April and May data from both years are not being aggregated together. Sometimes, if you only use the month name, Power BI will add all "April" values together, regardless of year.
How to create a Year-Month column in Power BI (DAX example):
YearMonth = FORMAT([YourDateColumn], "YYYY-MM")
Use this new "YearMonth" column as your x-axis.
By making these adjustments, your visualization will accurately reflect the trends for each period without creating misleading peaks.
If you want, you can share a sample of your data model or a screenshot of your current Power BI setup, and I can give more tailored advice.
Hope this helps you get a much clearer and more truthful chart!
4 Replies
Pls let me know what is that colors are representing. what is the best way to show this kind of data? Could you please elobrate your question indetail to address.
Thank you!
basically there are 2 sets of data, Canceled Booking and New Bookings, but as the duration runs from 2024 April to May 2025, there are 2 sets of data for April and May in years 2024 and 2025, hence they look to be peaked. This does not reflect the truths on the data.
I think it's something depends up on the data not on Visulaisation. Can you please look in to your data and make sure to use correct data from data base.
Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Hi tan_thiamhuat ,
This is a great question and a pretty common challenge when visualizing overlapping time periods in Power BI. What’s happening in your graph is that April and May from two different years (2024 and 2025) are being plotted on the same axis, so their values are stacking up and creating artificial peaks.
Here’s what you can do to show this data more accurately:
Combine Year and Month in Your Axis:
Instead of just using "Month" as your x-axis, create a new column in your data for "Year-Month" (for example, 2024-04, 2024-05, 2025-04, 2025-05). This way, Power BI will treat each month from each year as a separate point, and you won’t get overlapping values.Use a Hierarchical Axis:
In Power BI, you can set up your axis as a hierarchy: Year > Month. This lets you expand or collapse the data to see trends by year and by month, without mixing the same months from different years.Color/Legend for Data Types:
Since you have "Cancelled Booking" and "New Booking", make sure these are shown in the legend as separate lines or bars, so it’s clear which series is which.Check for Duplicates:
Double-check your data to make sure April and May data from both years are not being aggregated together. Sometimes, if you only use the month name, Power BI will add all "April" values together, regardless of year.
How to create a Year-Month column in Power BI (DAX example):
YearMonth = FORMAT([YourDateColumn], "YYYY-MM")
Use this new "YearMonth" column as your x-axis.
By making these adjustments, your visualization will accurately reflect the trends for each period without creating misleading peaks.
If you want, you can share a sample of your data model or a screenshot of your current Power BI setup, and I can give more tailored advice.
Hope this helps you get a much clearer and more truthful chart!
