Forum Discussion

JoCurry_Aus's avatar
JoCurry_Aus
Frequent Visitor
6 years ago
Solved

Date Filter Not Working When Using Gateway Refresh

The data I am working with ETLs to a data warehouse overnight and I refresh pbi datasets from the dw using sql via ODBC connection each morning.    I have created a calculated column in my dataset ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI JoCurry_Aus ,

    Today function will get different results when you use on the local and power bi server-side.  AFAIK, current power bi service side only supports UTC format DateTime, it does not do any date timezone conversions.

    In my opinion, I'd like to suggest you to use UTCNOW function and local timezone offset to instead today function.

    Formula =
VAR offset = 8
RETURN
    UTCNOW + TIME ( offset, 0, 0 ) - 1

    DAX function UTCNOW

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI JoCurry_Aus ,

Today function will get different results when you use on the local and power bi server-side.  AFAIK, current power bi service side only supports UTC format DateTime, it does not do any date timezone conversions.

In my opinion, I'd like to suggest you to use UTCNOW function and local timezone offset to instead today function.

Formula =
VAR offset = 8
RETURN
    UTCNOW + TIME ( offset, 0, 0 ) - 1

DAX function UTCNOW

Regards,

Xiaoxin Sheng

  • JoCurry_Aus's avatar
    JoCurry_Aus
    Frequent Visitor
    6 years ago

    Thanks for the feedback Anonymous - I will try this and mark as resolved if this works.

    • JoCurry_Aus's avatar
      JoCurry_Aus
      Frequent Visitor
      6 years ago

      Can confirm that the change to the formula has solved my issue - I am very appreciative Anonymous ! I am now using scheduled refreshes for the first time! Very important for everyone who need to reference 'TODAY' in a formula to understand how many hours the offset is to their time zone if they are using the gateway to refresh their datasets.

       

      My new formula looks like:

      DayCompleted = if(Dates[Date]<
      (VAR offset = 11
      RETURN
      UTCNOW() + TIME ( offset, 0, 0 ) - 1),
      "Completed","Future")