Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good Day Folks,
I am having trouble timing the release of Subscription Email reports.
Our PayWeek runs from Wed - Tues. The report is scheduled to run on Tuesday nights (11:00 pm Eastern) for the PayWeek just ended.
On my first run earlier this week, the Report PayWeek showed 44 instead of 43. My Dax formula for Report PayWeek is:
ReportPayWeek = LOOKUPVALUE( 'Calendar'[PayWeekNum], 'Calendar'[Date], Today() )
My supposed logic being on Tues Oct 27 at 11:00 pm, PayWeek should have read 43. Wondering if PBI Service is interpreting UTC time and flipping the calendar on me. Any thoughts on what might be happening and how I might resolve the issue would be much appreciated.
Thanks and kind regards,
Hi @rsbin ,
It might be the UCT time issue, but I just don't think so. One way to confirm/deny it is to put the date and time of the last refresh on the report and see what is happening. Put it in both local (EST) and UCT time.
But, I would suspect that the refresh itself is taking too long or not starting when you think it does. I would look at the refresh log for the report and see what time is starts and what time it finishes. I can see where if it doesn't finish until after 12:00 that you have crossed a "date line" and then the report might calculate an unexpected date.
AND, do you know for absolute fact that the data source you are using is in the same timezone as you? Maybe your report is refreshing quickly and at 11 EST but your data source is ahead of you or just not updated yet?
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
Proud to be a Datanaut!
Private message me for consulting or training needs.
Good Morning @collinq
Thank you for the response. I tried a simple test last evening. At 7:30 pm Eastern I performed a "Run Now" and the date was correct. At 10:30 pm, I performed another Run Now and the date did roll over to Oct 30.
I appreciate your ideas of putting a Refresh date and time to gain further insights. I do this on most of my other reports, but haven't done it on this one just yet. This is a small report so the refresh takes only 5 - 10 minutes.
My data source is in an Azure database. Now that I think about it, when an Audit gets posted from our App to the database, it gets registered as UTC time. I do some manipulation in Power Query to add a column to convert the time back to the Local Time where the audit was performed. Will do some additional testing this weekend.
Again, thanks kindly for your thoughts, suggestions and advice.
Best Regards,