Dayoffset = 0 is a day behind

Power Query
1

Greetings all…
I have a dates dimension with a ‘dayoffset’ column mostly to identify the current date for time intelligence calculations (e.g. dayoffset <= 0). Never had any issues with this before and have been using the same M function for a few years now. Recently, dayoffset = 0 is showing as the previous day in the Power BI service. I download the dataset, refresh the dates table which corrects this then upload the dataset again but when the dataset refreshes, it reverts back. I’ve done the corrections for UTC in power query. Even my timestamp datetime calculation is correct. I’ve uploaded a test report with four dates tables.

  • @Melissa Extended Calendar table
  • Radacad’s Dates dimension with a dayoffset column
  • My Dates dimension with a dayoffset column added
  • Dates dimension where I’ve added a column called DayOffsetDAX with a calculated column for dayoffset (which works correctly btw).

Any insight into this would be greatly appreciated.
Cheers
Dates Dimensions.pbix (587.0 KB)
all.

2

Hi @mkaess,

I see a couple of different M functions used to determine: “now”
My suggestion would be to replace that with: DateTimeZone.FixedUtcNow

So create a separate query: UTCnow = DateTimeZone.FixedUtcNow()
You can offset that to your timezone, refer to this query in all instances of your code so it’s only executed once. Other things to keep in mind.

  • Day light saving/summer time
  • Time the scheduled refresh is excecuted in the Service.

I hope this is helpful

2 Likes
3

Thanks @Melissa. Can you highlight in the query where you would make these changes please?

4

Hi @mkaess,

Like described above, create a single query you can refer to multiple times.

image
image1486×320 29.9 KB

Examine your code and where the Current Date and/or Time are required enter a reference to that instead. For example below is illustrated how to get the CurrentDate from the “Now” query.

image
image1135×158 15.2 KB

.
I hope this is helpful

1 Like
5

Hi @mkaess, did the response provided by @Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

6

Apologies for the delay in replying. Thanks for the help. Makes sense to me now.

7

The initial issue has not been resolved. Regardless of what I do, once the dataset is refreshed in the PBI service, DayOffset is one day behind. The timestamp query that is used to calculate the DayOffset is correct in a stand alone card visual but not in the date dimension. I’m uploading a more recent version of the pbix file.
Dates Dimensions.pbix (622.0 KB)

8

In my initial response I’ve already mentioned that you should also examine where (locale) and when (time and timezone) the scheduled refresh is taking place. Because if that is triggered before midnight the result is correct but it will be “a day behind”…

I hope this is helpful