Gatwway Data Refresh

[SteveM]

Hoping if some can tell me if this is the expected behaviour. 

The queries setup use the "Today" filter on the date column.

This seems to be the only way to automatically retrieve the data for TODAY, this does not seem possible when done in the report/dash itself.

This "Seems" to be the issue with the data not refreshing correctly

Each morning I have to open the reports in Desktop, refresh and publish. from then on the data is nicely updated via the direct query/gateway throughout the day.

When tomorrow comes, the data set does not update and the reports are stuck on the previous days data until I refresh and re-publish again.

Assuming this is the expected behaviour then how do we set the filter in the reports to only show date from "Today" without having to refilter the data manually, I cant see how.

I would have thought having the filtering done at the query level would have been the more efficient way to do this ? 

[Greg_Deckler]

Not sure I would call that expected behavior but I do not entirely understand how your queries are setup. One of the tricks for displaying "today" in the reports is to have a calculated column in either the query or DAX that essentially a 1 if it is today and a 0 if it is not today. In Power Query you use something like:

if [Date] = DateTime.Date(DateTime.LocalNow()) then 1 else 0

The equivalent in DAX is:

IsTodayDAX = IF([Date] = TODAY(),1,0)

You then set your report filter to only display rows where this column is 1. 

[SteveM]

Thanks, good tip to know but they dont look to be supported by direct query ?

[v-sihou-msft · Accepted solution]

@SteveM

You can consider adding a calculated column for tagging "Today" or not.

TodayOrNot = IF(Table[Date]=TODAY(),"Today","Not Today")

Then you can create a slicer with this column. Please refer to thread: 

http://community.powerbi.com/t5/Desktop/Direct-Query-Today-Date-function/m-p/57208#M23337

Regards,

[SteveM]

Thank you 🙂