Sending EOD numbers issue with UTC at month end

[nico]

Hi everyone.
I’m sending out the year to date numbers every evening around 11 pm. The intention was to capture at the end of the day so when switching months or quarters, the last email will show everyone the final numbers for the month/quarter.
I just realized, that unfortunately this does not work since the PBI server runs on UTC. I’m on pacific time. The screenshot below shows the email that was sent out on 2/28 at 11 pm and the month field already switched to March.

I found a lot of videos that show how to display the right update timestamp, but I didn’t find anything that addressed this issue. Since I don’t have a date/time field, but only a date field in my date table (I use the enterpriseDNA query for the date table), I don’t know how to accomplish this.
Thank you very much for any help.
Nico

[Keith]

Hi @nico

Check this out to see if this helps. This was posted within the forum. when i did a search Utc issue about 4 to 6 posting came up.

Local Date not UTC in PBI Service - Power BI / Online Service - Enterprise DNA Forum

Youtube videos are also available
see screen print

thanks
Keith

[nico]

Hi Keith,
Thank you very much. These are the videos I found and watched but for some reason I’m not able to wrap my head around how to make this work in my case. In the videos I watched, it was all about a time stamp. But I don’t have a time stamp. I only have a date stamp from my date table and brining in a time does not make any sense.
I apologize if this is a duplicate post, but I just don’t understand how this helps me.
Thanks,
Nico

[Keith]

could get the time stamp within the data file from the service to be included? then you can switch to your time that is done in the posting within the forum.

did you check out the posting link that i sent. that might help too

[nico]

I’ll think about it. Thank you very much. I’ll let you know how it went.

[EnterpriseDNA]

Hello @nico

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

[nico]

Hi,
apologies for not getting back. I carved out some time today to take a look at this and hopefully I will resolve this or come back with more questions.

[nico]

This is what my date table looks like

I used the EnterpriseDNA date query to get to it.

I connect the date field to the dates in all my other tables. There is no time field and I don’t understand how I should bring a time stamp in there and convert that to a date that is offset by 9 hours. Sorry for making this more complicated then it is but my brain just doesn’t get it.

[EnterpriseDNA]

Bumping this post for more visibility from our experts and users.

[ankit]

HI @Melissa - Can you please check this.

Thanks
Ankit J

[Melissa]

Hi @nico,

You could do something like this to offset a date by 9 hours from UTC
CurrentDate = Date.From( DateTimeZone.FixedUtcNow() + #duration( 0, -9, 0, 0))

As for the visual display, are you using relative datefilters? If so and its still giving you issues, you might want to switch to Offsets instead, from the code you’ve showed I can tell you’re using the legacy date table not the extended one. You can find that here.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

.
This extended date table includes Offsets, which are relevant to the CurrentDate on line 5, you will need to replace the M code with the line above - as depicted here.

I hope this is helpful

[nico]

Melissa,
Thank you so much for your answer and this detailed explanation. I have made the changes in my report. I’m hoping to see the changes tomorrow.
I tried to do some testing but it didn’t really work out so I decided just to wait.
I’ll provide an update tomorrow.
Thanks again,
Nico

[nico]

Looks like it didn’t work that way. It’s 5.15 pm now and the date already shows tomorrows date. Am I doing something wrong?

[Melissa]

I’ll give it a go later today and will get back to you

[Melissa]

Hi @nico,

It’s working as expected for me, ofsetting the current date in the date table by hours. As shown here, refresh took place April 7th but “todays date” in the date table is April 6th.

I’ve attached my sample, so you can give this a go as well. Determine the required number of offset hours and update the CurrentDate in inside the function query: utcCalendar

CurrentDate = Date.From( DateTimeZone.FixedUtcNow() + #duration( 0, -9, 0, 0))

eDNA - Offset Date by Hours.pbix (83.7 KB)

I hope this is helpful

[nico]

Hi Melissa.
Thank you very much. I can confirm that your file is working on my side as well. I will try to figure out where my file has an error and update.

[nico]

Hi again.
I copied your date query into my file and it seems to be working now. I had a lot of Measures using the today() function in my file. I replaced them all with measures referencing the date table. If you don’t hear back from me, this means everything worked. Thank you so much for your help!!!
Have a great weekend.
Nico