Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
In the past week or so we've noticed some odd behaviour in a handful of paginated reports in the PowerBI service; they're displaying UK date formats (dd/MM/yyyy) to the user, however when rendering, the data is being filtered using US formats (MM/dd/yyyy).
I can run the reports fine locally in Paginated Report Builder and we're using the latest version.
The reports are DAX based and have tested using DATEVALUE/CONVERT in the query but both express the same in the service; any other ideas on how I can force the locale to UK here? Or anyone else experiencing this?
The below identifies this issue locally and in the service:
DEFINE
VAR s = DATEVALUE(@StartDate)
VAR e = DATEVALUE(@EndDate)
VAR s1 = CONVERT(@StartDate, DATETIME)
VAR e1 = CONVERT(@EndDate, DATETIME)
EVALUATE
{
ROW("StartDate DATEVALUE", s)
, ROW("StartDate CONVERT", s1)
, ROW("StartDate Parameter", @StartDate)
, ROW("EndDate DATEVALUE", e)
, ROW("EndDate CONVERT", e1)
, ROW("EndDate Parameter", @EndDate)
}
Local
Service
Service Payload
***UPDATE***
I've come up with a workaround to use hidden parameters which force the locale to UK; this feels hacky but is allowing users to access data.
For each date, I have a hidden parameter i.e. StartDateHidden and this is formatted as: =FORMAT(CDATE(Parameters!StartDate.Value), "dd/MM/yyyy") and then it's this parameter being fed to DATEVALUE() in my DAX query.
Ideally this kind of step wouldn't be necessary!
Solved! Go to Solution.
Thanks @Tutu_in_YYC ,
This error is slighlty different in that the values shown on the screen are in the correct locale, and are even passed back to the report in the correct format; it's that DATEVALUE() is then rendering them in a different locale despite the model, browser, and user being set to UK.
The workaround I've highlighted above does the trick for now.
Thanks @Tutu_in_YYC ,
This error is slighlty different in that the values shown on the screen are in the correct locale, and are even passed back to the report in the correct format; it's that DATEVALUE() is then rendering them in a different locale despite the model, browser, and user being set to UK.
The workaround I've highlighted above does the trick for now.
Thanks for the additional information!
Hi BM,
The date format in the service is dependant on the browser's language settings. I came across the same issues, but for the standard power bi reports. See if this helps?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
60 | |
20 | |
18 | |
18 | |
9 |