date importing as negatives

[DazzaNin]

Since the beginning of April 2018, we've been having an issue with a drill-down, where dates are being converted to "negatives", and when importing the dates as pure text, the issue falls away. However, we then lose the ability to sort in order of date, if we then change the same field to a date type, we once again receive the "negatives" error.

The issue is not with the data as this is happening with any day within April 2018 from multiple databases and different datasets, is anyone else having this issue and is there a fix on its way, or a better workaround? 

It may be worth mentioning that all other dates work fine (from March 2018 to the beginning of time)

Error: "Negative values aren't supported and are being displayed as absolute values."

[ElliotP]

@DazzaNinIt might be worth checking the localization setting of your installed Power BI, it might have changed/be clashing after updating recently.

[DazzaNin]

Hi @ElliotP, great recommendation, both Localizations settings for the installed Power BI, have now attempted to change those settings as well in case that may fix the issue, unfortunately, it did not work though.

[Anonymous]

Are both your server and your local computer share the same date format?

What number format are your dates being stored in within the source?  For example are your dates stored as a whole number to represent "Days since 31/12/1899"?  Its important to know exactly what the data looks like in its storage, not how it is displayed by a reporting layer.

[DazzaNin]

Hi @Anonymous, Both the server and the local machine are using the same date format. 

Throughout the troubleshooting process, I've attempted to import as a VARCHAR, DATETIME and even tried converting to an INT format, VARCHAR allows the data to work and import, but converting to a DATETIME will break everything all over again, DATETIME does not work unless converted to a TEXT type after import and the int works as a sort order but will not work as a DATETIME. 🙂

Excellent recommendations though! 😄