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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.