Custom datetime column inconsistent formatting

[CassandraF]

Hi all,

I have a report with several datetime columns that have to be converted to local time from UTC. This is done with a couple of variables that check whether it's currently DST or not and then return the correct offset time, which is then added to the original datetime to return the local dtm value in a new custom column.

I am having an issue where one of these columns is returning dates from January as dd/mm/yyyy as intended, but then February dates are displaying as mm/dd/yyyy. This issue is not occurring with any of the other custom columns which are all configured exactly the same way.

Any ideas what is causing this to occur? I was not able to find any similar issue online. Both my system and the report are in the correct region, and the column is forced to dd/mm/yyyy in the formatting settings. Unfortunately I cannot share the report or dataset due to privacy reasons.

Any assistance is greatly appreciated.

[RossEdwards · Accepted solution]

On their own they look fine.  Next i'd check the 3 different locale settings and make sure they are consistant:
Set a locale or region for data (Power Query) - Microsoft Support

[RossEdwards]

I have run into this issue.  Basically the date formatting is getting tagged during your transformation process.  I would guess that you are likely seeing this issue crop up on dates that meet 2 conditions:

1.  The date is on day 1 to 12 of the month.

2. The date needed to have a UTC adjustment.

Are you doing this transformation in Power Query?  Whats the part of the formula that sets the date?  Could you change your formula to instead just add #duration(0, X, 0, 0)?

[CassandraF]

Hi Ross,

Thank you very much for taking the time to respond.

Here is precisely how the custom column is being calculated:

You are quite right that this is occurring for days 1-12 in the month.

[RossEdwards · Accepted solution]

On their own they look fine.  Next i'd check the 3 different locale settings and make sure they are consistant:
Set a locale or region for data (Power Query) - Microsoft Support

[CassandraF · Accepted solution]

Thank you. The data source is a Dynamics365 dataverse. The report region / locale was correctly set to Australia already however my device was using a "default" setting. I have adjusted this to specifically point to Australia instead and this seems to have fixed the issue.

Such a simple solution! Thank you again Ross for helping talk me through this issue. It is truly appreciated!