Date Filter Not Working When Using Gateway Refresh

[JoCurry_Aus]

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 called DayCompleted which simply states that everything equal to or less than TODAY()-1 is complete, everything else is future. I use this quite a lot to filter my data & visuals - its especially useful when displaying say a month to date actual vs budget so it compares only completed days. 

When refreshing from the desktop and manually publishing to the service from the pbix file it works perfectly. When refreshing from the gateway - either on demand or scheduled - anything that is filtered using this 'DayCompleted' field fails. I have also inserted a Card visual on each page of my published reports that displays 'Day Completed' in date format set to latest date so that all of the users know as of which date the data they are viewing is referencing. I expect this date to display as today -1. The total values are correct in that the sales data has refreshed up to and including today -1. Using today as my example (today is 7 October), the sales data has refreshed up to 6 October, but any visual filtered with 'DayComplete' only goes to 5 October.  So to be clear - in a simple table of sales by date the report shows the correct values against 6 October. If I apply the "DayCompleted' filter over this table I would only see up to 5 October. 

When I extract the pbix file after refreshing from the gateway, the 'DayCompleted' field shows that it has updated correctly in the dates table (6 October is Completed) and visuals including the card visual that reference 'DayCompleted' are fine. Any ideas how I can fix this so I can use the gateway refresh? I am desperately wanting to switch over to gateway refreshes but this is standing in my way. 

[Anonymous · Accepted solution]

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]

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

[JoCurry_Aus]

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