Incorrect end of month or last day

[wannabi]

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.

[amitchandak]

@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

[wannabi]

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 )

)

[v-yiruan-msft]

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 steps

Visual settings

Best Regards

[wannabi]

thanks again for taking the time and for your detailed response.

[wannabi]

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.

[v-yiruan-msft · 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

[wannabi]

Thanks ying!