Forum Discussion
Helper IChanging when 'Today' starts on the server
I have a slicer which shows me the date:
Formatted Date Full =
VAR SelectedDate = dim_date[PK_date]
RETURN
IF(
SelectedDate = TODAY(),
"Today",
CONCATENATE(
CONCATENATE(dim_date[Day Name], ", "),
FORMAT(SelectedDate, "dd/mm/yyyy")
)
)
I am in timezone UTC+12 hours.
When I work on the Desktop, it shows me today in a correct day, but on the PBI Service, it only changes to 'Today' at midday my time.
How can I have this so that Today starts at midnight my time?
I changed the Slicer date to: but still shows me UTC+0.
What else should I do?
Formatted Date Full =
VAR RawDate = dim_date[PK_date]
VAR SelectedDate = RawDate + 0.5 -- Adjust for UTC+12
RETURN
IF(
SelectedDate = TODAY() + 0.5, -- Compare using the same adjustment
"Today",
CONCATENATE(
CONCATENATE(
FORMAT(SelectedDate, "dddd"), ", "
),
FORMAT(SelectedDate, "dd/mm/yyyy")
)
)
Hi some_analyst
Use UTCNOW() instead of TODAY() to reflect the current UTC time. Note that UTCTODAY() always returns the UTC datetime at 12:00 AM, regardless of the actual time of day in UTC. Also, you can simply the date format to just FORMAT(SelectedDate, "dddd, dd/mm/yyyy") instead of using CONCATENATE.
6 Replies
sevenhillsSuper User
Could you try displaying what is the value you are getting for these using DAX and PQ:
Say,
NZ Date = NOW() + (12/24)
NZ Date2 = TODAY() + (12/24)
PW 1 = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),12,0)
AndrewPNZAdvocate I
This may be of help; to get the current date/time in New Zealand, in the past I have used a dataflow with a single table that just gets the current date-time UTC offset for New Zealand from a public timezone API, extracts the hour offset, converts the JSON result to table, and extracts the currentUtcOffset and then divides the seconds from this by 3600 to get hours (12 at the moment), and add that to UTCNOW().
https://timeapi.io/api/timezone/zone?timeZone=Pacific%2FAuckland
A bit of overhead to refresh this daily but it works. The specific API used above is just an example, but does look suitable.
- danextian
Hi some_analyst
Use UTCNOW() instead of TODAY() to reflect the current UTC time. Note that UTCTODAY() always returns the UTC datetime at 12:00 AM, regardless of the actual time of day in UTC. Also, you can simply the date format to just FORMAT(SelectedDate, "dddd, dd/mm/yyyy") instead of using CONCATENATE.
some_analystHI danextian I still have a problem with the date, thanks for the tip on the format though.
I used the formula:
Formatted Date Full = VAR RowDate = dim_date[PK_date] -- From your date table VAR CurrentUTC12 = NOW() + 0.5 -- Shift current time to UTC+12 VAR CurrentDateUTC12 = INT(CurrentUTC12) -- Strip time, keep just the date RETURN IF( RowDate = CurrentDateUTC12, "Today", FORMAT(RowDate, "dddd, dd mmmm") )And what happens when I upload the dashboard is the metrics don't add up when I click on the date, and when I click on other dates, 'Today' vanishes
this is when I open the report, metrics below do not add for the day, should be 0
when I click on other dates, the 'Today' is not there:
- danextian
NOW() is different from UTCNOW(). NOW depends on the timezone it is being evaluated.