Format

Formats a number or date to a string.

Set the measure formatting for the output in the Formatting field when creating a calculated measure, avoiding the need for the Format() function.

Syntax

Format(Numeric_Expression, String_Expression)

or

Format(Date_Expression, String_Expression)

Arguments

Numeric_Expression

MDX expression that returns a number.

Date_Expression

MDX expression that returns a date.

String_Expression

A string value that defines the formatting of the result.

For more details (and string templates), see how to Format string content.

Examples

Version release date

The example below will return a version release date as a string in the format DD-MMM-YY (e.g. 01-Jan-24):

'Version release - ' || + Format([Fix Version].CurrentHierarchymember.Get('Release date'), 'Medium Date')

See the example report Version report in our demo account. The calculated measure Predicted dates show several milestones on the timeline, including the version release date using the code above.

Issue created week

The example below will return the week of the issue created date property (use the "Issue" dimension at the issue level in the report to see the issue creation date property). If the issue is created on Sep 04 2024 then the measure will show the value W36, 2024:

Format(
  [Measures].[Issue created date],
  '"W"ww, yyyy'
)

Currency format

This format displays the result:

For positive numbers: Shows value with $ prefix, thousands separator and 2 decimal places (e.g. $1,234.56)
For negative numbers: Shows value in parentheses with $ prefix (e.g. ($1,234.56))
For null/empty values: Shows "N\A" text

Format(
  [Measures].[Original revenue],
  "$#,##0.00;($#,##0.00);\N\A"
)

Numeric value from the Issue key

Here is another example of how to extract and show some numeric values. The example below will return a string value of Issue Name that follows after - formatted to three digits. So if the Issue name is Demo-12, the returned result would be 012.

Format(
  Val(
    ExtractString(
      [Issue].CurrentMember.Name, 
      '.*-(\d+)', 1
    )
  ),
  "000"
)