Forum Discussion

Cul33's avatar
Cul33
Frequent Visitor
6 years ago
Solved

Date 1 day behind source date

I am importing various date fields from a SharePoint site - however, every date shows the previous day.    The SharePoint time zone is set for UTC Dublin, Edinburgh, Lisbon London and the locale En...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Cul33 - 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

     

     

slinfoo4's avatar
slinfoo4
Frequent Visitor
4 years ago

This still seems to be an issue with Power Query. I exported a Microsoft Access Table to a Sharepoint List, then accessed using power query in Excel to create some reports. 

The Dates are still a day behind. 

SharePoint is set to UTC - Dublin Edingbrough Lisbon London and Power Query is set to English United Kingdom.

It's a shame we still have to manually change column values to fix this.

 

Edit - After some more digging the values are only an hour behind. Due to the original date/times being midnight they result in being 11pm the previous day. This means I only had to add an hour on to correct the dates.