More Relative Date filtering issues

[Saifi]

I am having an issue with a published report contains visualizations created from transformed tables (Using Query Editor), the issue seems to be related to relative dates filtering.

Here is what I am doing, In Query editor open a SQL table data using DirectQuery, and then I filter rows using relative date filter, for example (Date.IsInCurrentDay([QueryTime]), and at the end I do some aggregations and groupings. The end product is a table that contains some stats\indicators specific to the above filtered date.

The problem happens when I try to view the published report that refers to that visualization, once the date passes, the visualization stop updating, it becomes static.

From what I can see, it seems that when I publish the report, the Power BI desktop passes the explicit date rather than the function to get the date. This is a big problem when creating reports that contain live stats and performance indicators, as the only way to work-around this is to republish the report.

Waleed Saifi

[Saifi · Accepted solution]

OK, here is what I found, and please correct me if any of facts are OFF.

When we open  a data source in the query editor (DirectQuery mode) and do the data transformation bit and include any relative date filtering, when we publish the report the explicit date is passed, so if we set date is current dat the explicit day/month/year is passed to the service, and the report will keep filtering on that specific date. However, when we get the data source (DirectQuery Mode) from the report page and filter against relative date, the report will keep the relative date peoperly and every day the report chnages the date in needs to filter against.

I am not sure if this related to the fact that in Query Builder we use M language and in BI report tool we use DAX?

Eitherway, it would be nice to see that fixed as Report level filtering is more effective and robust.

-Waleed

[v-lili6-msft]

HI, @Saifi 

Do you have refreshed the report and try it again.

Best Regards,

Lin

[Saifi]

Refreshing the report does not solve the problem as it is using the explicite date. When you refresh the repot it gets the data from yesterday, it does not flip to today's date.

I desigend the report to use relative date which equal Today.

That works as long as the date stays the same, once a new day comes in the report data does not change anymore.

-Waleed

[Saifi]

I am having an issue with a published report contains visualizations created from transformed tables (Using Query Editor), the issue seems to be related to relative dates filtering.

Here is what I am doing, In Query editor open a SQL table data using DirectQuery, and then I filter rows using relative date filter, for example (Date.IsInCurrentDay([QueryTime]), and at the end I do some aggregations and groupings. The end product is a table that contains some stats\indicators specific to the above filtered date.

The problem happens when I try to view the published report that refers to that visualization, once the date passes, the visualization stop updating, it becomes static.

From what I can see, it seems that when I publish the report, the Power BI desktop passes the explicit date rather than the function to get the date. This is a big problem when creating reports that contain live stats and performance indicators, as the only way to work-around this is to republish the report.

Waleed Saifi