Date formatting without conversion

[Unicorn_Tech]

Hello.  I have two date fields from Dynamics CRM.  One is user local, one is time-zone independant.

Both of them are converted to different time zones in Power BI.

Example: new_signature_date:  Dynamics = 11/12/2005.  PowerBI = 12/12/2005.  This is the time-zone independant field (defined in Dynamics.

2nd example:  new_signed_received_on: Dynamics = 12/12/2005.  Power BI = 12/11/2005 10:00:00 PM.  This field was defined as user local.

I need new_signature_date to function as a date, without any time zone conversion.  It should read: 11/12/2005 in all reports, including PowerBI.

Thank you

[Unicorn_Tech]

So, I wanted to give an update for anyone else who may have this problem.  Dynamics CRM's Time-Zone Independant field is not functioning correctly, it's adding a specific and random hour to each date, which is resulting in PowerBI finding something to convert.  I am speaking with Microsoft direclty about this problem.

[PhilipTreacy]

Hi @Unicorn_Tech 

Can you supply some real data?  Unless your date field contains timezone information I'm not sure how it gets transformed into other TZ's.  

Something like 11/12/2005 should be converted to the date 11/12/2005.

regards

Phil

[v-stephen-msft]

Hi @Unicorn_Tech ,

You can change the formatting of two date fields separately as follows.

Reference: Flawless Date Conversion in Power Query

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Unicorn_Tech]

@v-stephen-msft thanks - but how do I select an option to have NO CONVERSION whatsoever?  I just want the date as it was entered in the time-zone independant field in the Database.  PowerBI keeps asking for a conversion - but I want no conversion anywhere.  And it does need to be date, rather than text, for calculations.

[v-stephen-msft]

Hi @Unicorn_Tech ,

It is not converted into a date format, but if it is a date format. This sounds a bit difficult.🤔

You could try to find the same date format for conversion in Dynamics CRM, so that the converted date format is the same as in Dynamics CRM.

Sorry, this is the best method I can think of currently.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Unicorn_Tech]

@v-stephen-msft isn't this a strange problem to have though?  If you have a purchase date recorded in your database - that's the date the purchase was made.  It is very unhelpful to have PowerBI convert and play with the interpretation of the hours, to the point that the powerbi output can end up changing the actual recorded date.

[amitchandak]

@Unicorn_Tech , refer if this topic, if this can help

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=ccab07c3-...

[Unicorn_Tech]

Hi, thanks but it's not so much of a help.  I'm interested in getting the recorded date, without any conversion.  That thread speaks more about how to change time zones, rather than independant.  I also don't understand why it would take a time zone independant field and bother to change it like this.