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

[bigchippah]

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!

[bigchippah]

@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)?

[bigchippah]

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

[tackytechtom]

Hi @bigchippah,

According to Microsoft, this is a known limitation:

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/

[bigchippah]

@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!