Forum Discussion
Handling fall time change in continuous Line Chart (also, how to format the X axis?)
Hi all,
For context I'm using Microsoft Power BI Desktop v 2.151.1182.0 64-bit (February 2026).
Let's say I've got a SQL query that returns hourly data with the following fields: "Ept", "Utc", and "value". The first two are datetime2(0) fields, and the latter is just numerical. I've already created a measure for it which we'll also call "value" for the sake of it.
So I want to my "value" field on a line chart, ideally with "Ept" as the X-axis. When I drag "Ept" over into the "X-axis" field, it defaults to the full date heirarchy with Year, Quarter, Month and Day. I want to show the fully hourly data, so I right click on the field, and then select "Ept" rather than "Date Hierarchy". So far so good:
=>
and that changes my chart from this:
to this:
Perfect - that's what I want. But wait, what's that little blip to the left? Well, because November has two "01:00:00" hours, the value for those two hours is being duplicated:
How do I get it to display this properly without aggregating the hour? It seems MS BI does not make use of datetimeoffset data types, so when I return that format from my SQL query it just casts it as datetime, so I'm back in the same spot. So next I tried charting with Utc instead:
Great - that works. That's what I want to see, but now the problem is that my x-axis is now shifted by 4 (or 5) hours, so now my first hour of data starts at 11/1/2025 04:00:00 instead of 00:00:00.
So my question is how can I show my chart in EPT without duplicating the duplicate hour? I can create a text column with the date/time showing the full "m/d/yyyy hh:mm:ss TZ" for example, and display it with the X-axis set to "Categorical" mode. works, but then it tries to label every single hour and adds a scroll bar, eg:
So that's not what I want either.
Ideally, I really want the UTC chart with the EPT labels. Is that possible? Or at least possible to get something close?
Secondary question - far less important, but is there any way to change the default x-axis labels in a continous line chart?
it seems I can change the font details, color, units (not applicable for dates), decimal places, etc, but not change the format itself. If I try changing the format of the underlying column itself, it only changes it in the tool tip:
Hi mjc123 ,
There are potentially two workarounds in your scenario, You can address this by creating a new calculated column that explicitly handles the duplicate hour scenario instead of letting Power BI aggregate it implicitly. The idea is to build a column that detects when the same Ept timestamp appears more than once (like the repeated 01:00 AM during DST change) and slightly differentiates or tags those rows so they are treated as unique points on the axis. For example, you can use DAX to count occurrences of each timestamp and append a sequence or offset to the duplicate instances, which is potentially being used by you currently if I am not wrong. Another workaround is, you can handle this at the measure level by creating a DAX measure that intentionally ignores one of the duplicate timestamps so the visual doesn’t aggregate both values. The idea is to rank rows for each repeated Ept value (using a stable column like Utc), and then return the value only for the first occurrence while blanking out the rest. This way, the visual plots a single point for that hour without summing duplicates in Power BI.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
12 Replies
- grazitti_sapna
Super User
Hi mjc123,
Your issue is caused by Daylight Saving Time (DST) where 01:00 AM occurs twice in Eastern Time (EDT → EST change).
What i can suggest is instead of using UTC in the x axis and showing EST in tooltips or labels.
Created a sample .pbix for you, hope this helps.
Also, while i select the X Axis type to continuous, I am just seening the time and not complete time Stamp.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Thanks for the response - unfortunately, other than adding "Utc" and "Ept" to the tooltips, what you sent is exactly what I already had.
Working off the example you provided, I want that same chart, but I want the X-Axis labels to start at 12:00 AM, not 4:00AM. In other words, I basically want them to show what you added as "EPT Label", but in continuous mode so that not every single label is displayedHi mjc123 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Regards
Hi mjc123
I wanted to check if you had the opportunity to share the requested information. Please feel free to contact us if you have any further questions.
Thank you.Hi mjc123
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
