Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi, my actuals for November and December in power bi incorrrectly show an end of month date of 11/29/2021 and 12/30/2021 instead of 11/30/2021 and 12/31/2021, respectively, so my 2021 visuals are ignoring Nov and Dec data altogether. The database I'm importing from has the correct dates but the wrong dates are showing up in the power query preview and load. All other months have the correct last day. Has anyone seen this issue before and how do I fix it please? Thank you.
Solved! Go to Solution.
Hi @wannabi ,
Please refer the solution in the following links to update the date field in Power Query Editor:
Dates reverting to UTC when published to web from desktop
DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)
Resolving Timezone Issue on Power BI service
Dynamic time zone conversion using Power BI
Best Regards
@wannabi , what are using for max date like
Max('Date'[Date]) will be limited by data you have
eomonth(Max('Date'[Date]) ,0) ,will show end date
Thanks Amit. My calendar table is using both max and eomonth. Here's the code:
Hi @wannabi ,
The calendar table was created with no problem, showing the month end date for each month between the minimum and maximum dates in the 'actGens data' table. Based on your description, it seems that the dates in your actuals table are displayed incorrectly. The dates are correct in the data source (11/30/2021 and 12/31/2021), but when loaded into the model by Power Query, the wrong dates are displayed (11/29/2021 and 12/30/2021)... If my understanding is correct?
Which query (table) containing actuals data has this problem? The 'actGens data' table? Could you please provide the data in the data source, the data in Power Query and the data loaded into the model? And please share the applied steps codes in the Advanced Editor. Also, please share your visual settings (visual display, Fields panel settings, apply the filter in Filters pane? Slicer are applied? etc.).
Best Regards
thanks again for taking the time and for your detailed response.
Hello Ying, thanks for your input. you described the issue accurately. furthermore, here's what I learned yesterday:
The root of the issue is that the database is storing the data in the UTC time zone and PowerBI is importing it with the local time zone. The reason that the other months did not have an issue is that the conversion did not make it change the actual day, only the time on that same day.
so I think one solution (among a few) lies in importing and correcting the time zone. and I think there are several resources on how to solve this issue here in the community and on YouTube.
Hi @wannabi ,
Please refer the solution in the following links to update the date field in Power Query Editor:
Dates reverting to UTC when published to web from desktop
DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)
Resolving Timezone Issue on Power BI service
Dynamic time zone conversion using Power BI
Best Regards
Thanks ying!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |