topic Re: Scheduled Refresh Errors with the Dates in Report Server

Scheduled Refresh Errors with the Dates

Anonymous — Thu, 21 Mar 2024 12:23:50 GMT

Hi Everyone,
I have a Fact table with some sales data and a date column and a separate date Table created in DAX with the CALENDARAUTO(). The tables are linked together in the data model by the dates and They are used to generate a visual displaying sales trends over the years.

On the line graph visual i tried using the dates from the Facts table as well as the dates from the Dates table. Same case when it comes to the filters (I use a date timeline) and both approaches seem to work fine on the desktop power bi.

The problem comes when i publish the report to trhe report sever which has a scheduled refresh running every day.
After each refresh , if i use the date table dates on the visual and the Fact table dates on the timeline filter, the visual become blank for some reason, if i use the fact table dates on the graphs and the the Dates table dates on the filter, all the values get grouped to the first date only (When i move the timeline to later dates it shows blank).

I could use the Facts tables dates on both ends but i need to be able to drill down on the report visuals and this becomes not possible with this approach.

Any idea what this may be caused by and the solutions?
Thank you.

Re: Scheduled Refresh Errors with the Dates

lbendlin — Thu, 21 Mar 2024 22:05:53 GMT

Recommendation is to use the dimension columns for the axis. In your case the Dates table date. The facts table date column should be hidden.

Re: Scheduled Refresh Errors with the Dates

Anonymous — Fri, 22 Mar 2024 12:55:36 GMT

I tried this today and it looks fine on the power bi desktop and even after i publish it to thhe report server
The problem comes when the scheduled refresh runs, the visuals which have the date from the date table as the axis become blank, any idea what might be the reason?

Re: Scheduled Refresh Errors with the Dates

lbendlin — Fri, 22 Mar 2024 12:58:41 GMT

Timezone differences? The Power BI Service runs on UTC.

Re: Scheduled Refresh Errors with the Dates

Anonymous — Fri, 22 Mar 2024 13:03:24 GMT

I'm on UTC +3... any idea to adjust the data after it has been refreshed?

Re: Scheduled Refresh Errors with the Dates

lbendlin — Fri, 22 Mar 2024 15:27:06 GMT

change your filters to account for the service UTC.

Re: Scheduled Refresh Errors with the Dates

Anonymous — Mon, 25 Mar 2024 12:35:31 GMT

Thanks it looks good now