Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bigchippah
Helper I
Helper I

Need report to automatically adjust date at midnight local time, not UTC

Hello,

I know, I know.  ANOTHER local time to UTC time question.  Believe me, I have read through dozens of these posts and tried to understand my problem, but I am still very lost.  Please forgive me for asking again, and take pity on a poor lost PowerBI-er!

 

I have a report with a relative date filter so that it always shows "Today's" data (or SHOULD always show "today's data").  This report pulls hourly data from a SQL server. It updates with the most current hour's data and works without issue.  At least, until 8pm local time when everything goes blank due to it being midnight in UTC time and the date flips to the next day.  My report needs to continue reporting data for my (local) 8pm hour and beyond.

 

  • Current workaround is that I created a separate "today is" query that returns the Date.Time.LocalNow when I refresh the report.  This is not ideal as it requires me to open the pbix and refresh it each morning.  I'd much rather publish the report and have it update itself.
  • My date table is only date.  I have a separate time table, but the two are not related. 
  • My datetable is marked as a datetable.
  • I have tried to pull the date/time from my SQL server and use that, but I don't think I did it right because it's still not working.

Please be patient with me!  I'd love a step-by-step explanation so I can understand what I'm doing wrong and how I can get a solution (I'll need to apply this to several other reports, too). 

 

Thank you for any assistance you can provide!

4 REPLIES 4
bigchippah
Helper I
Helper I

@tackytechtomHi Tom,

Thank you for the reply! 

 

Disappointed to hear that Microsoft themselves have indicated this is an issue without resolution.  I've tried to use the RADACAD article before, but I think I must be missing some of the nuance. 

 

Regardless, I've gone back and tried one of their approaches that I hadn't tried previously: getting date/time from a webquery (in this case, their localtimes.info suggestion).

 

So, I've created a new blank query from localtimes.info that returns the date, time, and timezone of my locality.  I've marked the date column as my datetable.  I've related it to my "main" datetable so that my data is filtered to just "today's" data.  So far, so good.

But a few questions: my report isn't querying the web service at regular intervals, right?  It's not a directconnect connection, so it will only return the date/time whenever I manually refresh it, right?  So, what will happen at 8pm tonight (when my local time corresponds to midnight UTC time)?

As expected, at 8pm all of my data went blank once again.  Yet another dead end.

 

tackytechtom
Super User
Super User

Hi @bigchippah,

 

According to Microsoft, this is a known limitation:

tomfox_0-1651335450284.png

Use a relative date slicer or filter in Power BI - Power BI | Microsoft Docs

 

In this blogpost you can find some possible workarounds. Also, I think they are well explained:

Relative Date Slicer for Your Local Time Zone in Power BI - RADACAD

 

Hope this helps! 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

@tackytechtom I feel like I'm missing something because I keep reading articles that I don't understand and that don't seem to have any answers. 

  • My data comes from a DirectQuery connection to a SQL server.  Data is added to the server hourly and needs to be updated in my report hourly.
  • I would like the report to show "today's" data for the entirety of the day (in local time).
  • At 8pm, my data automatically changes to the next day because it's midnight UTC time.
  • I would like my report's date to change at midnight local time (EST) and begin displaying that day's hourly data WITHOUT having to manually refresh the report.

I do not understand how the following things relate to each other: creating a DAX solution with a -4 time offset (UTC to EST) and using this to prevent my report's date changing at 8pm.  Why is it so hard to tell the Power BI service: do not change my report's date until 4 hours after UTC midnight?

 

Please help!  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors