Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have noticed the date column in desktop shows a day behind from the date value I have in SharePoint.
I created a Document Library in a SharePoint site to store images with a 'name' column and a 'week commencing' column. When a date is entered into the 'week commencing' column e.g 22/04/2024, and PBI is refreshed with the data, the column values in PBI shows as 21/04/2024 instead of 22/04/2024.
I have checked Regional Settings in both SharePoint and PBI and I cant see any discrepencay there.
TIA
Solved! Go to Solution.
Hi @yaman123 ,
Base on your description, it seems like that the date column in Power BI Desktop showing a day behind from the date value you have in SharePoint. The possible reason for this issue could be that the time zone settings in Power BI Desktop and SharePoint are different. Please check the time zone settings in both applications to ensure that they are the same.
Check the settings of Sharepoint:
Date is showing a day behind in SharePoint Online date field - Microsoft Community
In addition, the following links are the threads which has the similar problem as yours, hope it can resolve your problem.
Solved: SharePoint dates show in Power BI as one day off - Microsoft Fabric Community
All dates are stored as utc and time shifted in the browser to match the regional settings. That's why bst causes a problem because some dates are +1 hr and some aren't. To fix it i took an export of the list to excel and detached it from the list so it didn't update. Then i set the time zone of the sharepoint site to utc, then ran a flow against it looking for dates that had a decimal in them (excel stores dates in weird format of number of days past 01/01/1900), then rounded the date up, formatted it and wrote it back to the sharepoint list.
Solved: Date 1 day behind source date - Microsoft Fabric Community
Smells like a timezone problem but hard to say. I suppose you either need to track down the discrepency in the date/time setting. Dublin is +1 UTC so that's almost assuredly where your issue is coming into play. The time is some date at 12 AM, you subtract an hour and it is the previous day.
So, your options are probably to track down the source of who is assuming UTC (probably Power Query/Power BI) and make it see reason, or you could fix it with a calculated column. In Power Query that would be:
Date.AddDays([Column1],1)
In DAX:
[Column1]+1
Best Regards
Hi @yaman123 ,
Base on your description, it seems like that the date column in Power BI Desktop showing a day behind from the date value you have in SharePoint. The possible reason for this issue could be that the time zone settings in Power BI Desktop and SharePoint are different. Please check the time zone settings in both applications to ensure that they are the same.
Check the settings of Sharepoint:
Date is showing a day behind in SharePoint Online date field - Microsoft Community
In addition, the following links are the threads which has the similar problem as yours, hope it can resolve your problem.
Solved: SharePoint dates show in Power BI as one day off - Microsoft Fabric Community
All dates are stored as utc and time shifted in the browser to match the regional settings. That's why bst causes a problem because some dates are +1 hr and some aren't. To fix it i took an export of the list to excel and detached it from the list so it didn't update. Then i set the time zone of the sharepoint site to utc, then ran a flow against it looking for dates that had a decimal in them (excel stores dates in weird format of number of days past 01/01/1900), then rounded the date up, formatted it and wrote it back to the sharepoint list.
Solved: Date 1 day behind source date - Microsoft Fabric Community
Smells like a timezone problem but hard to say. I suppose you either need to track down the discrepency in the date/time setting. Dublin is +1 UTC so that's almost assuredly where your issue is coming into play. The time is some date at 12 AM, you subtract an hour and it is the previous day.
So, your options are probably to track down the source of who is assuming UTC (probably Power Query/Power BI) and make it see reason, or you could fix it with a calculated column. In Power Query that would be:
Date.AddDays([Column1],1)
In DAX:
[Column1]+1
Best Regards