Not showing the correct date values in service even after creating the time zone custom column

[WinterGarden]

Hi All,

I am facing an issue in which the date values from the sharepoint list is showing as one day before the actual date in powerbi service.
In order to solve that issue i've created a custom column , similar to the steps mentioned in this article:
https://cloudbi.com.au/converting-utc-to-local-datetime-in-power-bi/

these are the steps i've performed:

1) created a custom column using below code:
DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([Project Start Date],5.30))
2) converted the type of the column to date

even after creating the custom column it is showing incorrect dates.. one day before in most cases..

faced similar issue earlier in another project. In that file as well i've created the same custom column.. and it was showing the correct date in service after schdule refresh.. only difference is that the date format of that column is general

 and in the one i am facing this issue it is long date

does anyone know what is the issue?

[v-tangjie-msft]

Hi @WinterGarden ,

Thanks to Jai-Rathinavel  and shafiz_p  for their quick replies. Did their replies help you resolve your issue? If so, please accept the helpful replies as solutions to help users with the same problem, and if not, please point them out.

Best Regards,

Neeko Tang

[Jai-Rathinavel · Accepted solution]

Hi @WinterGarden , You must first identify the TimeZone which is followed for populating Project Start Date. Based on the time difference you can tweak the m code of your custom column

If you notice about 7 hours and 30  mins difference consider below code

[Project Start Date] + #duration(0, 7, 30, 0)

If you find 1 day difference use the below one

[Project Start Date] + #duration(1, 0, 0, 0)

If you couldn't back track the timezone, consider manually tweaking the numbers inside #duration untill you get the correct date.

Did I answer your question ? Please mark my post as a solution 

Thanks,

Jai

[WinterGarden]

Hi @Jai-Rathinavel ,
This resolved the issue.. Thank you!

[shafiz_p]

Hi @WinterGarden  Instead of using DateTimeZone.RemoveZone, try using DateTimeZone.ToLocal to convert the time to your local time zone. For example:

DateTimeZone.ToLocal(DateTimeZone.SwitchZone([Project Start Date], 5.5))

Try converting the date to a standard format before applying the time zone conversion.

Verify the locale settings in Power BI Service. You can set the locale for your dataset in the Power BI Service settings to ensure it matches your local time zone.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz