Issue: Power Query function Date.IsInCurrentDay() doesn't use the correct date.

[grazzhopper]

First off

We are new to the PowerBI cmmunity, but PowerBI impresses us so far. Very nice product and we hope to contribute to the community in the future. We think we have encountered an issue and hope if you guys can help us out or if indeed it is a bug it could get registered and fixed.

Setup

We are using the Direct Query functionality on an Oracle database with the following Query where INSDATE is the insertdate of the record. The Enterprise Gateway connects the database to the online PowerBI environment. Within PowerBI desktop the report gets filled with all relevant records of the current day and using the publish gives the expected results also within the online environment.

let
    Source = Oracle.Database("alias_adres", [HierarchicalNavigation=true]),
    DBASE = Source{[Schema="DBASE"]}[Data],
    UNIT1 = DBASE{[Name="UNIT"]}[Data],
    #"Filtered Rows" = Table.SelectRows(UNIT1, each Date.IsInCurrentDay([INSDATE]))
in
    #"Filtered Rows"

Issue

Whenever a day passes the report within the PowerBI online environment it still keeps showing the records of the day before and not for the current day as we would expect. Within the PowerBI Desktop environment, after a refresh we see the records we would expect from the currentday. So the functionality works allright there. A renewed upload of the .pbix file corrects the issues on the PowerBI online environment.

Suspected problem

Hence, we suspect that Date.IsInCurrentDay() function uses the uploadday of the .pbix file as reference to the current day and not the system/server/current as value for the query. This would be unwanted behaviour of the functionality for us as a daily manual upload seems to be a bit tedious and get in the way of us taking a holiday now and then.

Consideration

We are considering using a view on the table with the currentdate within the Oracle database layer to overcome this problem, hower, as Synonyms on the Oracle Database don't work with PowerBI, it might be highly possble that views won't work either. We will have some testing done next week with the datase admin to make sure if creating a view is a viable band-aid for the issue and will keep you update in this thread on that.

Questions to Microsoft/PowerBI Community

Is this a known issue?

Is this only an issue with Oracle databases?

Does anyone has other workarounds or solutions other than sugggested at our Considerations?

Or did we something obviously wrong in using the functionality and should we use something different?

Love to hear from you and hope you can help us out!

Thnx in advance,

Grazz.

[v-haibl-msft]

@grazzhopper

There are two known and longstanding issues around “current date”:

1. Using “current date” in the query editor when doing Direct Query against SQL Server, Oracle or Teradata results in a query which hard-codes the actual current date at the time the query is saved.
2. The timezone in the service is always UTC, so references to the current local time will potentially produce values which are offset from the values the user expects.

If you want a LocalNow that is consistent between Desktop and Service you can use this expression: DateTimeZone.RemoveZone(DateTimeZone.UtcNow())

You can also take a look at this article.

And there are two different approaches for using the “current date” with SQL, Teradata or Oracle Direct Query: 

1. Use a hand-written SQL query instead of building the query in the query editor.
2. Return all the data from the query editor and apply the date restrictions in the modeling layer.
   
   

Best Regards,
Herbert

[grazzhopper]

Hi @v-haibl-msft,

Thanks for your elaborate answer and suggestions. Greatly appreciated!

We've added a view of the table with the right parameters on the Oracle production database and the Navigator picks up on that seamlessly; Works like a charm. No needs for a handwritten query, but is great to have that option available too if the need arises and you pointed out that option to us.

It would be awesome that the relative dates will be added to the filters within a direct query for those managers that have a more "strained" relationship with their database administrator. 😉

I hope this thread will be a help for others that run into the same issue until it gets resolved in a future version.

Thanks again for the great support,

Grazz.