How to Use FORMAT Function in DAX in Power BI

Use the FORMAT function in DAX to convert a value to text with a specific format by writing FORMAT(value, format_string). This helps display numbers, dates, or times in a readable way in Power BI visuals or calculated columns.

Syntax

The FORMAT function has two parts:

• value: The number, date, or time you want to format.
• format_string: A text string that defines how you want the value to appear, like "#,##0.00" for numbers or "MM/dd/yyyy" for dates.

The function returns the formatted value as text.

DAX

FORMAT(<value>, <format_string>)

Example

This example shows how to format a sales amount as currency and a date as a short date string.

DAX

Sales Amount Formatted = FORMAT(Sales[Amount], "$#,##0.00")

Order Date Formatted = FORMAT(Sales[OrderDate], "MM/dd/yyyy")

Output

If Sales[Amount] = 1234.5, then Sales Amount Formatted = "$1,234.50"
If Sales[OrderDate] = 2024-06-15, then Order Date Formatted = "06/15/2024"

Common Pitfalls

• Using FORMAT converts numbers or dates to text, so you cannot perform further numeric calculations on the result.
• Incorrect format strings can cause unexpected output or errors; always use valid format patterns.
• Locale differences affect date and number formats; specify formats explicitly to avoid confusion.

DAX

Wrong: Total Text = FORMAT(Sales[Amount] + Sales[Tax], "$#,##0.00")

Right: Total Amount = Sales[Amount] + Sales[Tax]
Total Text = FORMAT([Total Amount], "$#,##0.00")

Quick Reference

Format String	Description	Example Output
"#,##0.00"	Number with thousand separator and two decimals	1,234.50
"$#,##0.00"	Currency with dollar sign	$1,234.50
"MM/dd/yyyy"	Date in month/day/year format	06/15/2024
"dddd, MMMM dd, yyyy"	Full weekday and date	Saturday, June 15, 2024
"0%"	Percentage with no decimals	75%

Key Takeaways

FORMAT converts values to text using a specified format string in DAX.

Always use valid format strings to avoid errors or unexpected results.

Formatted results are text and cannot be used for further numeric calculations.

Use FORMAT to improve readability of numbers, dates, and times in reports.

Test your format strings to ensure they match your locale and display needs.