Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wannabi
Regular Visitor

Incorrect end of month or last day

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.

1 ACCEPTED 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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: 

Calendar lookup = FILTER (
CALENDAR ( MIN ( 'actGens data'[Date] ), MAX ( 'actGens data'[Date] ) ),
[Date] = EOMONTH ( [Date], 0 )
)

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.).

Applied stepsApplied steps

Visual settingsVisual settings

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks ying!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.