Calendar Table won't update on scheduled refresh, but does with a manual refresh

[bdv99]

I have a calendar table that is created by M code. The code itself works fine, however I've got a problem where the calendar will not refresh as part of the daily dataset refresh scheduled for 5am.

If I subsequently refresh manually via PBI desktop, it's fine. 

Why do I need to refresh a calendar each day ? We use date offset functions, and yesterday should be daysoffset -1. this offset is not updating until I manually refresh.

Any ideas welcome !

[lbendlin]

Remember that Power BI Service runs on UTC.  You likely have some time zone timing issues.

[bdv99]

thanks, but unfortunately this isn't the issue. The refresh occurs, data is loaded but the calendar table doesn't update. If I update manually from within desktop, the calendar function runs correctly and updates.

[lbendlin]

 please show the Power Query code for your calendar.

[bdv99]

sure, it's this code here

Solved: M Query - Week Number - Microsoft Fabric Community

[lbendlin]

oh wow.  I would recommend you take it easy and start over with your own query. Better yet, use an external Dates table (how often will it change?),  for example an Excel file in a SharePoint.  Only include the columns you need, and only the date range you need to fully cover your data.

[bdv99]

Thanks, the code runs perfectly - indeed it's code you can find on many forums as an example of a dynamic calendar table. The issue doesn't appear to be the code. If I refresh the query within PBI desktop, it works. If I refresh via the service it does not.

In anwer to your question how often does it change ? Daily. I particularly wanted the offset feature, with days offset 0 being today, and days offset -1 being yesterday. This value is not updating on a Service based refresh.