Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a date table that uses a fixed date as the start date of the date Table and Date.From(DateTime.LocalNow()) as the end date. So the date table should always be updated to go through today. It works great in Desktop but as soon as I publish the report, it doesn't work in the Service. The end date stays the date that I published the report and isn't updating. Any advice? Is there any way to get it to work as expected? Thanks!
You have to refresh the report. DateTime.LocalNow() only changes on a refresh. Think of it like the TODAY() function in Excel. It doesn't change until there is a recalculation necessary. If you just put that in cell A1 of an spreadsheet and leave Excel open, it will never change.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans oh, yeah, sorry, I've obviously tried refreshing the dataset, refreshing the visual and I tried clearing the browser cache which is the only suggestion I could find online. None of those worked.
Does this happen for days? DateTime.LocalNow() reports the server time, not your time, and the server is on UTC. So it will be several hours off and appear not to change. To force it to be your time, try this code, where -8 is my timezone offset, so adjust for your offset.
DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8))
I go into more depth on this in this article. And it has worked for years.
Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics
Otherwise, you'll need to post the code for the query you are using as something else is preventing that code from being read/used.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk, thank you. Yeah, it's not just a few hours off, I published the report on 2/10/2023 and then tried the refresh on 3/10/2023 and it was sticking with the 2/10 date. I re-published the report on 3/10 and it is still stuck on 3/10 when I refreshed today (3/14). I am using a rather complicated M code for the date table, so it must be something in that code (it is way beyond me, I just copied it from online). I will try a more simple date table and see if that works.