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

     

     

lachlanP's avatar
lachlanP
Helper II
1 year ago

I have another option for a solution that I've used. I have a date/time/timezone column that has 00:00:00 UTC as the time component and I only want to extract the date, not fussing with timezones. 

What I did was to convert the column to text, ex. 

2025-01-08T00:00:00Z
Then use the following to strip out the date before the time component by using the T as a delimiter:
=Text.BeforeDelimiter([Start DateTime], "T")

 

Then convert this column to a Date type. This makes sure there is no timezone interfering with the date value.