How to Use FORMAT Function in Power BI

The FORMAT function in Power BI is your go-to tool for controlling how your data looks, turning raw numbers and dates into polished, readable text. Mastering this one DAX function can make your reports significantly easier for your audience to understand and interpret. This article will walk you through exactly how to use the FORMAT function, from basic number and date formatting to creating custom layouts for your reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the FORMAT Function in Power BI?

In simple terms, the FORMAT function in DAX takes a value (like a number or a date) and converts it into a text string based on a specific format code you provide. It’s perfect for situations when Power BI's default formatting options in the ribbon aren't quite flexible enough for what you need.

The official syntax for the function is:

FORMAT(<value>, <format_string>, [<locale_name>])

• <value>: The number, date, or time you want to format. This can be a hardcoded value, a column reference, or a measure.
• <format_string>: The code that tells Power BI how you want the value to look. This can be a predefined string like "Currency" or "Long Date," or a custom string you create using special characters.
• [<locale_name>]: An optional argument that lets you specify a regional format, like "en-US" for the United States or "de-DE" for Germany. If you leave this out, Power BI uses your computer's regional settings.

You can use the FORMAT function to create either a new calculated column or a new measure. The key thing to remember is that the output of the FORMAT function is always text. This has important implications for sorting and further calculations, which we'll cover later.

How to Format Numbers in Power BI

Let's start with the most common use case: formatting numbers. Whether you're dealing with sales figures, percentages, or plain integers, FORMAT gives you precise control.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

General Number & Currency Formatting

For standard number formatting, you rely on placeholders like 0 (which shows a digit or fills with a zero if empty) and # (which shows a digit or nothing if empty). You can also add commas and decimal points.

• General Number: The simplest option, returns the number as-is.
• Format with Decimals: Force a certain number of decimal places.
• Include a Thousands Separator: Add a comma for better readability.
• Zero Padding: Useful for things like invoice numbers or identifiers.

For currency, you can use the built-in "Currency" string or define a custom format by adding currency symbols directly into the format string.

• Standard Currency:
• Custom Currency Symbol:

Percentage Formatting

When you use the percentage format, FORMAT automatically multiplies your number by 100 and adds a percentage sign (%).

Formatted Pct = FORMAT(0.758, "Percentage")
 // Result: "75.80%"

If you want more control over the decimal places, you can create a custom string.

Formatted Custom Pct = FORMAT(0.758, "#0.0%")
 // Result: "75.8%"

Displaying Large Numbers (Thousands/Millions)

Manually creating dynamic labels like "$1.5M" or "250K" is a great use for FORMAT. You can achieve this with a clever trick using commas in your format string. A comma after the thousand separator tells Power BI to divide the number by 1000.

• Format in Millions (two commas):
• Format in Thousands (one comma):

How to Format Dates & Times in Power BI

Formatting dates and times is where FORMAT truly shines. You can display dates in nearly any combination you can think of by using standard and custom placeholder strings.

Standard Predefined Date & Time Formats

DAX offers several convenient, built-in formats to get you started quickly.

• "General Date" or "g": Shows date and time. (e.g., "6/1/2024 1:45 PM")
• "Long Date" or "D": Spells out the month and day. (e.g., "Saturday, June 01, 2024")
• "Short Date" or "d": The standard numerical format. (e.g., "6/1/2024")
• "Long Time" or "T": Shows the full time. (e.g., "1:45:30 PM")
• "Short Time" or "t": Shows hour and minute. (e.g., "1:45 PM")
• "Month Year" or "Y": Displays the full month and year. (e.g., "June 2024")

Formatted Long Date = FORMAT(TODAY(), "Long Date")

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Custom Date & Time Formatting

For complete control, you can build your own format string using placeholders. Here are the most common ones:

• Days
• Months
• Years
• Time

Here are a few practical examples of how you can combine these in a calculated column or measure:

// Example:
// Create a clean "Month-Year" label for charts
Month Label = FORMAT('Calendar'[Date], "mmm yyyy")
// Result: "Jun 2024"

// Format for a file name or log entry
File Date = FORMAT(NOW(), "yyyy-mm-dd_HH-nn")
// Result: "2024-06-01_13-45"

// Create a friendly, readable date for a card visual
Readable Date = FORMAT('Calendar'[Date], "dddd, mmmm d")
// Result: "Saturday, June 1"

Crucial Best Practices and Caveats

While FORMAT is powerful, its most important characteristic - converting values to text - can cause problems if not handled carefully. Keep these points in mind to avoid common pitfalls.

The 'Text Type' and Its Impact on Sorting

Because FORMAT outputs a text string, your results will sort alphabetically, not numerically or chronologically. This is a very common source of frustration for new Power BI users.

For example, if you create a "Month Label" column ("Jan 2024", "Feb 2024") and use it on a chart axis, Power BI will sort it alphabetically: Apr, Aug, Dec, Feb, Jan... That's not what you want!

The Solution: "Sort by Column"

The best practice is to always have a numeric column to sort your formatted text column by.

1. Create your text-formatted column (e.g., Month Name = FORMAT([Date], "mmmm")).
2. Create a corresponding numeric helper column (e.g., Month Number = MONTH([Date])).
3. In the Data view, select your "Month Name" column.
4. In the "Column tools" ribbon, click "Sort by column."
5. Select your "Month Number" column from the dropdown.

Now, when you use the "Month Name" column in visuals, it will sort correctly: January, February, March, etc.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

FORMAT Impedes Further Calculations

Once you’ve converted a number to text, you can no longer perform mathematical functions like SUM or AVERAGE on it. Power BI treats a value like "$1,234.56" as a string of characters, not a number you can add up.

The Best Practice:

• Use FORMAT on your final measure, the one you are displaying directly in a visual like a Card or Table.
• Keep your base measures and calculations as raw numbers. Don't format them to text until the very last step.
• Whenever possible, use Power BI's built-in formatting options in the "Format pane" for your visual. They apply a visual formatting layer without actually changing the underlying data type, preserving proper sorting and calculations. Use FORMAT only when those options are not sufficient.

Using Locale Codes

The optional third argument in FORMAT allows you to display data according to a specific region's customs, which is great for international reports.

// Display currency in German format (Euro symbol, period separator)
Euro Currency = FORMAT(5432.10, "c", "de-DE")
// Result: "5.432,10 €"

// Display date in French format
French Date = FORMAT(TODAY(), "D", "fr-FR")
// Result: "samedi 1 juin 2024"

Final Thoughts

The FORMAT function in DAX is an essential tool for turning standard Power BI reports into polished, professional-looking dashboards. By understanding its text-based output and learning the custom format strings for numbers and dates, you gain the fine-grained control needed to present your data with clarity and impact. Remember the best practices around sorting and calculations to ensure your visuals are not just beautiful, but also accurate.

While correctly formatting DAX measures is key to building good reports in Power BI, it's often the last of many manual steps. If you prefer to get straight to insights without wrestling with DAX, we designed Graphed to automate that entire process. You can connect all your data sources and simply ask questions in plain English like, "show me year over year revenue growth by month," and our AI builds live dashboards for you in seconds, with all the formatting and calculations handled automatically.