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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.