Relative date filter on visualisation not returning data

[NevZab72]

I am a PBI newbie and I am having a problem with a relative date filter on a visualisation. It is not returning all records for the period specified. When I run the SQL profiler to see what's going on, I can see PBI generates a WHERE clause as follows:

WHERE [MyDateField] IN (DATE1,DATE2,DATE3,etc) 

Of course, if you report on the last 10 years, we're talking 10 x 365 (ish) dates listed in the sub query. Could this be the problem?

Has anyone else experienced this or can anyone offer advice?  

[amitchandak]

@NevZab72 , what is the option you have selected?

[NevZab72]

It's worth noting that I can replicate the problem in SQL Server Management studio by copying the query from SQL Profiler. So surely I guess it is a limitation of SQL in dealing with the code supplied by PBI. Here is teh WHERE clause... 

WHERE ([DateCreated] IN (20200712,20200726,20200713,20200727,20200714,20200701,20200715,20200728,20200702,20200729,20200703,20200716,20200730,20200717,20200731,20200704,20200718,20200705,20200719,20200706,20200720,20200707,20200721,20200708,20200709,20200722,20200723,20200710,20200724,20200711,20200725))

It seems fine for short relative periods, but with longer it does not return all records. 

[v-xuding-msft]

Hi @NevZab72 ,

I'm afraid that the problem hits the limitations of the relative date filter.

The following limitations and considerations currently apply to the relative date range slicer and filter.

• Data models in Power BI don't include time zone info. The models can store times, but there's no indication of the time zone they're in.

• The slicer and filter are always based on the time in UTC. If you set up a filter in a report and send it to a colleague in a different time zone, you both see the same data. Unless you are in the UTC time zone, you and your colleague must account for the time offset you experience.

• You can convert data captured in a local time zone to UTC using the Query Editor.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range#limitations-and-c...

[v-xuding-msft]

Hi @NevZab72 ,

Have you resolved it? Can you please accept the answers that you make sense as solution? Others who have the same issue will benefit from this thread.

[NevZab72]

Sorry for the delay. I can't give a definitive answer at this stage as I change my approach due to teh frustations I was experiencing. I suspect that a resolution was not forthcoming, but I cannot be sure until I attempt to replicate the problem.

[NevZab72]

I have tried many option but the one i'd like to use is, RELATIVE DATE  [IS IN THE LAST]  n YEARS

[amitchandak]

@NevZab72 , report an issue or log an idea for a query not optimized

https://community.powerbi.com/t5/Issues/idb-p/Issues

https://ideas.powerbi.com/forums/265200-power-bi-ideas/