Measures using FORMAT([Date], "Short Date", "en-us") generate error on Power BI Report Server

[johncpratt]

I have confirmed a reproducible error generated on Power BI Report Server for measures that format a date using "FORMAT([Date], "Short Date", "en-us")" syntax.  The error is resolved using "FORMAT([Date], "m/d/yyyy")" syntax.  The error does not occur in Power BI Desktop.

I am using Power BI Report Server September 2021 GA - Ver. 15.0.1107.166, with the corresponding Power BI Desktop - Version: 2.97.2222.0 64-bit (September 2021).  PBIRS is running on SQL Server 2019 Enterprise CU15.

The error received is as follows (it is misleading, but reproducible):

Unrecognized fields in this visual
We are not able to identify the following fields: Text Measures[Last Purchase Date Display]. Please update the visual with fields that exist in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
More details: Could not resolve QueryDefinition due to invalid Measure reference 'Last Purchase Date Display' in the SourceEntity ''.'Text Measures'. This error often happens after the Measure or Entity was renamed or deleted. Please update the query/visual to use the new name or undo the name change.
Request ID: 5f750aa5-98c6-73a4-b337-4a605a4f33ee
Time: Wed Jun 29 2022 09:18:42 GMT-0400 (Eastern Daylight Time)
Service version: /powerbi/libs

[v-xiaoyan-msft]

Hi @johncpratt ,

Please correct me if I have misunderstood.
You are using Power BI Report Server, if you use "FORMAT([Date], "Short Date", "en-us")" syntax to format the date will generate the following error.
But you can solve this problem by using "FORMAT([Date], "m/d/yyyy")" syntax.

The problem is not reproducible in Power BI Desktop, do I understand it correctly?

Best Regards,
Community Support Team _ Caitlyn

[Anonymous]

Hi guys

I have the same issue with PBI Report Server Sept 2021. It is related to this new feature of the Sept 2021 release:

Making it easier to format based on a user’s locale

The error concerns only Power BI Report Server.

Hope it will help for the troubleshooting.

Have an amazing day!

[johncpratt]

@v-xiaoyan-msft ,

Yes that is correct.  During troubleshooting I was able to narrow down the issue to measures with date formatting (there is one other measure in the same measure group that worked perfectly), and then selectively removed and re-added DAX to find the exact cause.

@Anonymous , thanks for the info on the precise feature!

[Anonymous]

Hi @johncpratt 

The May 2022 release of Power BI Report Server fixes this issue (I don´t know if the January 2022 release also fixes it because I haven't used it). Maybe you should try.

Have an amazing day!

[johncpratt]

@Anonymous ,

Thanks!  I just installed May 2022 in mid-July after figuring out how to fix the visual rendering issues with pie and donut charts introduced with the January 2022 release (which I promptly removed from my TEST environment).  I have made a note to test the date formatting with the May 2022 release and will post back with my findings.

[johncpratt]

@v-xiaoyan-msft , @Anonymous ,

I can confirm that the bug listed in the OP is resolved as of the May 2022 release of Power BI Report Server.  I was able to modify my measures to use the "FORMAT([Date], "Short Date", "en-us")" syntax and tested them successfully.

FYI, I had also installed the CU 16 update for SQL Server 2019 Enterprise just prior to installing the May 2022 PBIRS release.