DAX format() function does not work as specified for months and minutes

[pade]

Please update the DAX Format() command to follow the specification: https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function

According to the specification, MM shall be months and mm shall be minutes. In reality both returns months unless also hh are included, and then it only returns minutes, no matter if mm or MM are used

Try the code below and you will see:

FORMAT(DATE(2001;02;03)+TIME(04;05;06);"MM") & " " & FORMAT(DATE(2001;02;03)+TIME(04;05;06);"mm") & " " & FORMAT(DATE(2001;02;03)+TIME(04;05;06);"hh:mm") & " " & FORMAT(DATE(2001;02;03)+TIME(04;05;06);"hh:MM") & " " & FORMAT(DATE(2001;02;03)+TIME(04;05;06);"yy:mm") & " " & FORMAT(DATE(2001;02;03)+TIME(04;05;06);"yy:MM")

[v-qiuyu-msft]

Hi @pade, 

I have reported this issue internally: CRI 161494361. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @pade, 

I got information below: 

The format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic (OLE Automation), not on the format strings used by the .NET Framework. 

Also please refer to this Visual Basic document that has the correct format string information https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-v...

where:

mm	Display the month as a number with a leading zero (01–12). If m immediately follows h or hh, the minute rather than the month is displayed.

Best Regards,
Qiuyun Yu

[pade]

The documentation does not say the same: https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function I.E. as a developer you shouldn't care about format specifications for other products, that's just confusing. You must make sure the documentation is up to date, and that the documentation reflects the intention of the product.

According to the documentation mm shall return minutes and noting else. And if we should use the formats in the link provided, I see no posibilities to just get the minutes.

So please rethink how this shall work. At least the product shall act according to the official documentation 😉

[v-qiuyu-msft]

Hi @pade,

Thank you for your feedback. I have delivered your concern internally, will keep you update here. 

Best Regards,
Qiuyun Yu

[pade]

I tried the "nn" formating defined in the Visual Basic settings jut to confirm that the implementation is according to the Visual Basic specification, and that was true. My guess is then that this was implemented when the extended formatting settings was intruduced for a cuple of months ago. If that is the case, we now have an implementation that 1) Is not according to specification, and 2) is not backwards compatible with how it worked before.

And connecting this with Chris Webbs  blogposts about how the extended formating options works where he has discovered that the DAX specification is not as good as the Visual Basic specification are, I guess you really need to adress this to the product team so they can decide the future of formatting before it's to late  

[v-qiuyu-msft]

Hi @pade, 

I got information that "As I wrote previously, current document is WRONG. We have informed the document team to update the doc. ". 

Best Regards,
Qiuyun Yu

[pade]

Looks like its hard to get this correct. The ”nn” formatting for minutes without proceeding "hh” is implemented but I don’t see this in the documentation. 

For us developers, it’s much easier if you write the documentation based on how the product is intended to be implemented an a wild guess is that the “nn” is intended

Since “nn” was forgotten even this time, I would  you suggest you to do a quality check on the whole documentation regarding formatting

[v-qiuyu-msft]

Hi @pade, 

Thank you for your feedback. 🙂 I have delivered your concern internally. 

Best Regards,
Qiuyun Yu 

[v-xuding-msft]

Hi @pade ,

The document had been updated. Please take a look at it. Hope it is helpful to you.😉