Date Range Filter Not Working When Using Gateway Refresh

[Anonymous]

Dear Community,

I have a date filter set up in a report I created through Power BI desktop as follows. The data filter is set to on or after 11/12/2021 and on or before 10/03/2022. When I update the data in the data source and refresh the report in power bi desktop the data works fine. I have published this report to the service and am using a gateway to connect the report to the excel data source that the desktop report is using as well. When I refresh the report that is on power bi service using the gateway, the date filter is not working anymore.

For example the total value within this date range should be 252 as per the below table in the desktop version however the service version's total is 219 so it seems like some values have been excluded in the Power BI service version of this report after using the gateway. All other tables that do not use the date filter have not been impacted by the gateway refresh and are displaying as intended. What can be done to resolve this issue? 

Thank you for your help.

Table in Power BI desktop below displaying correct total of 252.

Table in Power BI service below showing incorrect total of 219.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

This due to the time on power bi service is different from the on-premise. DAX works with the date and time of the system that hosted the Power BI file. Power BI is a could based service, and that means Power BI files will be hosted on a server somewhere in the world, that might not be on the same time zone as your city is. So as a result when you used functions that works with the current date and time; such as TODAY() or NOW() in DAX you will not get your local current date and time in Power BI Service.

In your case, you can check there some date is before 10/3 but  on service is not.

For more detials you can refer this post.

Solved: UTC to Local time in Power BI Service with DayLigh... - Microsoft Power BI Community

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hariharan_R]

Hi,

 Power BI Desktop or Power BI service both are able to connect the data source then both should display the same results. Do the basic checks again if not done already,

 1. Check whether you have mapped the gateway data source in Power BI dataset under dataset settings. FYI - Gateway data source will be displayed to map only when you have access.

 2. Once mapped the gateway data source, refresh the dataset and wait for to complete

Thanks

Hari