When writing date/Time to an Azure SQL Database changes to the date/time can happen.
This is why:
Azure expects date/time in UTC, and when PowerBI reads it, it adjusts the date/time to the current timezone. This is why the time and, consequently, the data changes. It's an HTML issue within the report.
I’ve tried to visualize this below.
In version 1.0.75.2 and earlier, where the DATE format is used in accoPLANNING/Masterdata, a time of "00:00:00" is added when writing to the SQL table. This can cause issues when the Azure database is in a different time zone. The SQL database expects data to be stored in UTC, so the time is recorded correctly. However, when PowerBI displays the data, the time difference may be either added or subtracted from the UTC time, which can cause the date to change as well. For example, a date entered in January 2024 in accoPLANNING will be written to the SQL database as "01.01.2024 00:00:00." If the database is located in CMT-0 and the user is in CMT+2, PowerBI will display the date as "31.12.2023 22:00:00."
Our solution is to append "12:00:00" instead of "00:00:00" when sending the date to the SQL server. This adjustment will resolve most of the issues. However, it will take some time before this change is available in AppSource due to our testing procedures and Microsoft’s approval process. We expect it to be available by Q2 2024.
In the meantime, you can manually append the time in the view you use to expose the data to PowerBI:
SQL
CAST(CAST([DATE COLUMN] AS DATE) AS DATETIME) + '12:00:00' AS NewDateTime