URL filtering by date is broken

[edgreg]

Trying to follow the documentation here and add some filters on my URL and having trouble getting date based filters working. I've gotten string based filters working using the eq operator, but I cannot get the gt/ge/lt/le operators to work on dates.

I have a date column which is a datetime value from SQL. I have derived a new column in my query to convert this datetime to a string using the function CONVERT(VARCHAR, EndTime, 120) AS szEndTime.

I have tried to filter on both the EndTime native DateTime column, as well as my derived szEndTime column:

• ?filter=Builds/szEndTime eq '2018-10-03 15:00:00' works, but not using the gt/ge/lt/le operators.
• ?filter=Builds/szEndTime gt 2018-10-03T15:00:00. This does not work.
• ?filter=Builds/EndTime gt '2018-10-03 15:00:00'. This does not work.
• ?filter=Builds/EndTime gt 2018-10-03T15:00:00. This does not work.

How can I get my report to filter on dates via the URL?

[v-danhe-msft]

Hi @edgreg,

Based on my test, it could work on my side, you could refer to below steps:

Sample data and report:

When I published to the Service:

Enter the code:

https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date eq 2018-01-01

Result:

Could you please try it again?

Regards,

Daniel He

[edgreg]

Did you try other operators other than eq?

As I mentioned in my post, the eq operator does appear to work. It's the other operators (gt/lt/ge/le) that do not appear to work correctly.

I cannot access your report to test...I get an error stating "Sorry, we couldn't find that report". I believe this is caused by the "me" portion of the URL https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date eq 2018-01-01. I believe me should be a guid.

[v-danhe-msft]

Hi @edgreg,

Based on my test, with below code, it could also work on my side:

https://app.powerbi.com/groups/me/reports/cf91650f-9248-40c6-b08a-317a64a011ec/ReportSection?filter=Table1/Date gt 2018-01-01

Result:

I think it may due to your code with your date, could have tried with the below code again?

?filter=Builds/szEndTime gt 2018-10-03 15:00:00

Regards,

Daniel He

[edgreg]

Hi Daniel,

Nope, adding the below did not fix my issue:

?filter=Builds/szEndTime gt 2018-10-03 15:00:00

Interesting that leavig the time off does work:

?filter=Builds/szEndTime gt 2018-10-09

How can I get the time portion to work correctly?

Thanks,
Ed

[v-danhe-msft]

Hi @edgreg,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @edgreg,

I have tested with below format and it could work, I suggest you test again. 

?filter=Table/Date gt datetime'2018-09-27T09:55:00'

Regards,

Daniel He