Forum Discussion
Handling fall time change in continuous Line Chart (also, how to format the X axis?)
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
Super UserHi 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.
RegardsSorry - I was on vacation, just got back last night.
in terms of the most recent response from grazitti_sapna, I'm seeing this:The problem is that now it looks correct, but we're back to the issue of aggregating that Ept hour. As you can see from the data you generated yourself:
There is no row with a Value = 27. Instead it's adding 12 + 15 for the two "1:00:00 AM" hours.
To be clear, using your data there, I want to see this chart:
but with this x-axis labelled as some variation of: "12:00 AM, 01:00 AM, 01:00 AM, 02:00 AM, 03:00 AM, 04:00 AM, ..."
I say variation because we could even do something like this:
"12:00 AM ED, 01:00 AM ED, 01:00 AM ES, 02:00 AM ES, 03:00 AM ES, 04:00 AM ES, ..."
Anonymous, you asked for "sample data that covers your issue or question completely, in a usable format". You could honestly use the data grazitti_sapna created, but here is a full month which is more applicable to what I'm doing:let base64Str = "fZ1bjiw3DkS3MvC3C04ylXp4K4b3v43JwTQf4lH1r+tAYChY0Uol6/qff/4Q+euSv/TS5z/t7+v648/8X67//5ePzuuPf//c4AewGNyfCnfAavCjFR6Ab4NHr/AE3LzmWeEF+HF4VVgu0D8yPvcloAX0+IVW0NNoxVbLDXr52qRpo4SP2EA5GGlO3sLVaaW4l+sGTjNNzVs7i6GdJufdGG4jDRV3dI2KKx01OW9roQGUlpqcd3Xi9FTcVGnAaaq4q3d8g3T7Jh7UvHSrNCtX9/QZlWbhpuVdWyvNuvU+yFRkyq7k9RMiH8BedQPcv8M6KzwAh8KrwhOwC7ylwgtwNOyCLxfo57vnOVN20ce1FfT4rjFnyi77pdEhQhu9VxVbIgcfPYEWvJGDk/63ZFInvYwEGiydbnoCnYqhn5FAgm1UGhoJNOGo0tFIoJSehtPSSKD0t9BweuoJpE+sfiNSiviXlkqzcg8gTZFyI1KK9Pd4oJVm3R5A2q9Ksxc9gLRh7dSLu+63bCzdAcv3/RuAow0BT8Bec58VXoD9UDMajLlARwdCYc6UXfT79xiblzNlV71lvdE3aM+Ua4FuoONUw/ajj+lUg+2Wg5O/NKDQy8iUztXpZmQKvRf6KXFKhVSlofKLo0pHj6caw2lpZEo6HhpOTyNTRgRcQ6YU8VuUN2RK0f6moVaahUemzFZp1h2HmmtWmr0YhxqFytSLu+4X7hXugP2ccmHlAdiLFqw8Acc5BXu9APuftfT33iq8QMc55QItoD1THhiTM2VX/dIwJmfKLns7MRndQHumCGn66Jly2G45OBlPStxDeilhpgCnm5Epk6vTz3RO4XeHhsrp2ddwOhqZMrGRSksjUzo6QOlpOqfE6g8ypYj/aGrGB5lStL/0qDQLj0xJj1UPMqUo3+4xHmRKEb6dJh5kyq77PdSg7A7Yq26oYwD2ohvKmIDjbCUVXoD9z1pbMOYC7R3YYHrOlF30RxUSc6bsqk825kzZZZ92JGfKrvuthGvTxzinKGUenIwGxIYLvZRfOlDoZmRKm8DpZ5xTuDFKQyNTbhSjdDQyRbg6LY1MSX9lDaenkSlXfIE6MqWI/8gEzcojU7g2C/dMkQWadXumyOqVZi9Gplx3pVMv7rq3De/IlF321uYdmbKrfmGp8AQcNUPhAuwtmP6CW4UXaO/A9MRhtIDubjoKyZmyq/7I4No36Ok0G6qBjvbj2vQxMkXgjRycjEy5gNPLyBQ6L3QzMkW4Ov2MTGExSkMjU4RfNToamdK4Oi1NmUKcnkampLPEQKYU8W82z0qzcj39wR/IlCJ9ez4dyJSi/K2kVZq9mM4pUunUi7vu7apmIFN22af9G4BPf9oGMmUXvR09BjJl13za6pwpu+btqsZoAe0N+HTQCjrOySg7Z8oue7vDMLqBjkdvVkIf49nnIn5w0q1caCmhl/Hsc6FNhG6mt0TE6Wd6S4Rvg9JQz5T7Ik5HJa7dH+C0VOKODBup9DTeEqX7gIlMKeLfbZdKs/LjfcpEphTp2+PsRKYU5dsmTmRKEb7d1Uxkyq5769yJTNlln3ZkAD7d0U5kyi76XRllLMDt+8o5U3bNJ4U5U3bRR1pBj19a5AY9f1m7gY72g41CH+WX/pODk9GAXJ1eHt8822d0M733IU4/I1MufndoaHrzfAGno5EpwtVpaWSKYmeUnkam3CF1IVOK+M+d/tAuZErRflqbhUempLeyC5lSlG+vFRcypQjfXvYvZMque4urhUzZZW/XhguZsqveYnMhU3bRn7tB4QIcf9awcs6UXfNbB7Y6Z8ou+qUXaAUd75I76Bu0t9/DShpob7+GzRb6GOeUdGS3zw5ORgPCd6GXKVO4LXQzzinCYuhnOqdgG5WGxjmF/isdjUxhbyktlV88VXoamRIbKRcypYjPIyRGs/LIlNYqzcJTpjyVZt1pmOWqNHsx3vu0XunoxaI7h5t91AF71Tc0DsCRKavCE3C894EzC3D7vtUpU4rm/ErJaQHdv7dIypSiOuem0zfoOCZz7QY6nn0ENH2Mc0pntx6cjIdv+C70Mt3RchPpZpxTbrS30M+4T1F0rNLQOKfwy6N0NN2noHalpXGf0rAzSk/TfUrs+z58ehCfL2uMZuXx7JOacR8/PUh/K5mVZt1xnxL3GEazF0/3KUanXqxDt3EjYB91wPHeB3UMwDHSAHgCjvsU7N4C3L4LzJlSp24fmJ4zpUzdputCpxW0998FjTlTytStxpO60w304b2P0/QxMiV9jTlGW5WfvgpCL+Vw4e6f0c10R8va6adniixsutLQyJQLXx6lo54p6X2I47Q0MkUgVelpeu8TOMZoq/htYzBGW7VvLYAx2ir9VAnrTu99RqXZi3oyFHO0RffbW73CHfDhXbJ9NABHGz4VnoAjU64KL8CeKR1wzpQyR5um+pwW0J4pHcbkTClztPkswTnaIjtfGzjdQMfMG/ZP6GO6o+XiByfjoMxdoZdxR9tYDN2MZx9FVwn9jGefG9uoNDSefRq/anQ03dFCqtLSePZhKyo9TfcpEXCYo63iP3c6TWKOtmp/SwHNwuPZhzTrTvcpoNmLaZBfKp16sczRphsp+6gDlkPfYo62qN5O+pijLaI33zFHWzTneVSv8AIdd7QDtIA+DEg5raDj10Gkb9BxR4sdyZlS5mh1cW36mO5o4bocnDyML/pn9DIyhf4I3Uz3KVRKP9MdLSxSGpre+3TgdPT03sdxWpre+2AjlZ7GOSVumgRztFV8HgE3mpXHOWXMSrPwOKekkwfmaKvyzSLM0Vbh+YWB0akXyxxtGly2jzpg+Q4PwPq9jAn48AMR+2gBjhZ8YMwFOt4SdNACOga5ubaCjnMKbMyZUudo4wWE0w10nFO4Nn1Mc7QC/OBkXI1dwOllZMrD0ulmZMpNnH6e7mi9i2hoZAr3UeloyhTsjNLSyJQLTa70VE6HT8zRVvH5lZXRrDwypfdKs/A0mz8qzbrjPiU1OuZoq/DtIgNztEV3fi9rH3XAh9/72EcD8OEnIvbRBBzPPlC4AMeVHlbOmVLnaK8GWkDH0OUEraB95m2h7JwpZY5WFitpoOPRm2vTR8+UNBfpnx2cdCsFTSL0Mu5T0kU352ir9vzjN/+MfqaZN0hVGpruaLG60tG4o1WuTkvjPuXiF5mexjklZTnmaKv4bdsxR1u1b3cNmKOt0rfvEOZoq/ITzV5MvyHslU69WOdoFXAHfLqjxRxtUb3dY2COtojerkYxR1s0bzejnKMtmreU4BxtEb1ddHGOtqjeUoJztEX2R1J+c4626P5IJ00fI1PSGZJztFX5i7NyeumZcsLppmdKzlrO0VbxecDYu4iGeqZIx+pKR+OO9vDNpKWeKaLoAKWnnimSvkCYo63iP5LaC3O0VfsnfitpMOv2SEkv8jBFW3V/hlaYjeiBsqAw9WGZoU1fCIzQFsWfgXUHWCv4ATvBWr0De7zAtq/r5igp47Pp+8vp2aL3MwkrYGu6B+blHCmzsx17nGOkjM42Nhzd8xRJr3c5OFtFfx4WQgfle88JPZTvJgpd9AR5sNlKGz1AUn5wZrYKP7SI0kiPj8a16WQ85UTwYWC26v6k99eYl62yP+kghXHZqvqT7oQxLVtFf9IbBwzLVs2fCTh1X5mVXaiig/XswF4MsFbwxFZMsFZvelWDOdkiN7c/x2SL3MMW5+woU7LpBMch2SL44HTOjjIj21hzAxyjJ4DpXpo8AX3wzw8gLIQOenZQIi101ayaHppqVKG00EWjN5QWumbshtJCk6xcmRaa5DR8hYHYqji9qMI4bFUcP1w2lgWb4I51Wa/pnWDZcSY3HTUwBlvVpvskTMFC7V3ZAdbUpidWzMBWtRf2bIFtX9kcF0XuhSJyXOx6F8TltKh6L8A34Pm9igb4R/GEHULvXPHFMg7u/UheLIP2meaY0/dP6J9pHiyDBprm+Pf4vGVooIle/DbRwNCMopUWmujD0rTQRMeDm2LUtWqeWlmW7JIvLMyKXXL8jTKYFYfiq8JsOuU30ODouqKY8jrQn4LjTsw+GEC1tpB9MIHeNQntgwW01YD1yi6wP0obtiBlRZHaUG3KiqK1c90b7I/YOIA728D+qG1k6ZnJvdFmcnDtR29jA9M3E3yA6ZwpjsONf0Lv/FABgUrzTHOc6h2me6a5wz6lfaaZnan0zzTHQ6VifLVKjgd3Y1mxKY5rCWNZsAmOZz5jWa/pXWDZbxESKCI1XJEbE+j2SQfrsYaNGGD9WHFVdoLFKcg+WEDbty3LObGLjXdNzgpYywnsWM6JXWzjujfYH7HKdRtYU4uWFNoWVxeAD76Z3gWYxvHhwz+hc66Ye0HvTPKNlZXmmeYHNSvdM80p2wymfab5xj4r/XPNsTJGUqvkq6Is2AX3yrJe19sqy3JNbjwAGct2M7XxiG5sarciFiV0oILdxQxq0cpiJ9Abf8cxf1qUpj/NnD4tSuMZ0FkB27+yCtZaDF2TI2IXCxdyQpShU8GOCT2LdyRoMjnYJt/KoG/xfoR7TOvSCDtoupcmw/DVoH9p2BQ0HfS7zQWYFvrVJmF6GO9FgsaYaVX9aZVlyX5LUVEWHP9a0agwC/ZrzRSZmC+tgj8PykhtV8ZL01Ee06VFb7pStE8GWL9P75WdYL1esAus9VuDczkvymRpOpBysLTo/XRUkROjzJV2+JEjo4yVci9yaJSp0sWV6Z6HxuJ2HPzzlhN0htDClBtsfboY71X5rRIaGe9VL6yutNKjg/ui9NKjY5KmmfFW9UKjKO2Ug58YKK3KP3EvYzDL9vcicmFplu0JEq8zDWbRniBpRzBNWkUfFKYeLMOkAwt3sJ4gq7IDrEceNm6C9Rcj2IkFtn3diJwgZY40LvccFsDWdPEG1mEFbD03ufIN2DtOADfA1nGdMN3zBEkZyQnSKjq9+/GP6GA8mzyg6aHnR2PddDFGvUArbYyDB3Zb6WP6R0NA08j0+37QdDL9zyqcxuRo1f2Jt0UGs2qTfVeUJceNZ2VZsCkeFWXjKR8vMS5axKLYDtJaDlswgJrSp6ITqN2YVXKBbN9E5bgoOhtYAevDXGAVrEsFe4O1F3DYhBwVu9p068PB0Cr3sPDBNHuupDraJt9aTOibCT4sTOtMsaJ3lN4JL6o5C1olxw89HaZ7JrmxDNrnV53hCaZAq+T4CbuxrNgUxxSbsSzYrzq1sqzX9CZxmP6scjvY1G672vRHDJOfRWzMMtkHA6h1GladQK3WXtEFtH0rIEfErnTB3hwRu9SFanNE7FoXa7jBuljCDbDJVcJ0zQWnRwYOelIymlLoXYjmftA+l53yilOe0N2gUumhC1esrXTRhTd4rvQxhPOLSifjwjPWxoAndKcTFuY7ITud1THfCdXpgQHznRCdehXznVVzGqc2OHXfLlkF+jpYMVYrO8D+FJzmv+2TCdbqTWdezHcWuXrw4wL8Izf9CsZhAdxtZcIK2OZ7HpiXs2NXrAN7kbNjl6xsC6F7pjn9lMk/OvhnJ4yLa9NBU51+2esf0UN/n3qomy76KYP9rLTRhKefUzpNH014+uc8naaRPqnFupVOmvD0zyYr5jur7gPMqtWdlAqzaFOd/k+SBrNmE51+Qmswu8+zI42XYL6zSE6/RbVPOlirOP0Rx3xnEZz+qU37ZIK1s20MjdknC2z7um7Ojl1u+rmiwwK4s50531kEpx9XO3wDtkcTYQ81wDYcmF5fc76zar4fdJwc/LPsGFybDprqm3YLPfQHlBjo9Y/oor9kHdhspY0m/FCJ0kd/RplwXWmkv2fll0XppI95pvM55jyr7vS/QDKYVfs1Rrpax6RnVX2AWbO/bU2vUDHrWTWn3w8bnLpvl5z+cUr7pIOVr+sOsF4w1p1grd50C4VhzyI3/a+dvLoLsPXcgyJydux6D22Rs2MXnHuf055F8QlugK3hHjgtdC+yY4E++Octx82jg54dk2vTQ8+Ozi8KXfTsaFCptNGz44Y1Sh89O9imSiM9O9ioSic9O+Ja5sbUZ9WdRBrMqj07YjraYBbt2cGVWbNnR/SIwew+z464OzA4uq9IPlTRwXrrtcoOsFZwPFvYJxPsjdawTxZYP3eATdlR5KZ/od9hAWwtJ9jilB1FcPrHURy+Afu54wHcAPu5gyvTvRgApcKDf37uuEDTwcgOdLPQQ8+OxbXpomfHhI1KGz07OqxR+ujZMUjTSM+OOKU4TSdNeLOJavX/P/ahbLPSTrIBs2qT3ezHrwGzaFPdBCuzZs8Oe8AJmN3n2fG/u9d//ws=", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(base64Str, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Utc = _t, Ept = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Utc", type datetime}, {"Ept", type datetime}, {"Value", Int64.Type}}) in #"Changed Type"
If I drop that same code into grazitti_sapna's example, I get this:which would be fine, except for that blip on Nov 2nd:
You said, "Please show the expected outcome based on the sample data you provided.". It's hard to do that because if I knew how to do that I wouldn't be here. But I'll approximate it for you:
I achieved this simply by adding 30 minutes to the second 01:00 hour such that my raw data looked like this:
Maybe that's the solution. I'll have to adjust my SQL query to do that, but it's really a hack job and not "technically" correct. It boggles my mind that it never occurred to the devs that a user might want to chart a sub-daily timeseries chart with X-Axis labels in a timezone that includes a duplicate hour in the fall time change.
- grazitti_sapna
Hi mjc123,
Please see the attached file, is that what is required? As the data in this file is limited and utc date is starting from 4:00 a.m onwards so the x axis is startigng from 4 am, I've replaced X axis with ept and it's showing correct dates. also x axis is already continuous.
