Dynamic parameter for datetime is 12 hours off

[MarshallWilley]

Hello, I'm having a problem with a report page that uses a dynamic parameter for the date from the selection on a slicer to change the SQL query. It appears to work except that the resulting data seems to start at 12pm instead of 12am as seen below. And I can confirm in the database that there are entries before 12pm.

The parameter passes successfully to the database query using the Power Query code snippet below pasted from the Advanced Editor. The 2nd usage of it defines an endpoint 24 hours later, which appears to work as intended (endpoints stop at 12pm next day), but again shifted the same 12 hours.

Where Test_History.Entry_On Between '" & DateTime.ToText(StartTimeParameter,"yyyy-MM-dd hh:mm:ss") & "' And '" & DateTime.ToText(Date.AddDays(StartTimeParameter,1),"yyyy-MM-dd hh:mm:ss") & "'

I have the parameter defined like this...

And it is bound to a date table like this...

I've tried changing around the format options for each of these linkages, to no avail. I'm not sure what could be adding/changing the 12 hours. Any ideas?

[rohit1991 · Accepted solution]

Hi @MarshallWilley ,
In your Power Query code, change the datetime format string from "hh:mm:ss" to "HH:mm:ss" to force 24-hour time (midnight), and always convert your parameter to midnight with this:

DateTime.ToText(DateTime.From(Date.From(StartTimeParameter)), "yyyy-MM-dd HH:mm:ss")

 This will ensure your parameter always passes 00:00:00 (midnight) and fix the 12-hour offset problem.

[MarshallWilley]

Thanks for your help, that worked!

[danextian]

Hi @MarshallWilley 

Your're using TODAY in your calendar formula which value depends on where it is being evaluated. In Desktop, it is based on your device's timezone. The service though uses UTC so intead of using TODAY directly, get the current UTC datetime first and add to or subtract several hours from it based on your UTC offset. Try:

StartDateTAble =
VAR _TZOffset = 8 --for UTC+8
RETURN
    CALENDAR ( DATE ( 2022, 12, 27 ), INT ( UTCNOW () + DIVIDE ( _TZOffset, 24 ) ) )