cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoCurry_Aus
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

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

Can confirm that the change to the formula has solved my issue - I am very appreciative @v-shex-msft ! 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")

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.