Forum Discussion

MarshallWilley's avatar
MarshallWilley
Regular Visitor
1 year ago
Solved

Dynamic parameter for datetime is 12 hours off

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 see...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

danextian's avatar
danextian
Super User
1 year ago

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