Power BI DAX FORMAT Function: Dates, Numbers, Currency, Percent and the Blank-Row Fix

The Power BI DAX FORMAT function turns a number or date into text with the exact look you want: FORMAT([Total Sales], "$#,0") gives "$4,905" and FORMAT('Date'[Date], "mmm yyyy") gives "Mar 2025". It is perfect for titles, labels and tooltips. Because the result is text, don't use it on chart values. For those, use a format string.

This guide is for Power BI developers and analysts who know basic measures and want clean labels without breaking their visuals. You get every number and date code in tables, copy-paste DAX, a worked example with sample data, and fixes for the three problems FORMAT causes in real reports.

In this guide

• What the DAX FORMAT function does
• How Power BI DAX FORMAT works: syntax and rules
• DAX format number: predefined and custom codes
• DAX format date: every code with examples
• Format for any country with the locale argument
• Worked example: a dynamic card title and a growth label
• Common FORMAT errors and how to fix them
• Dynamic format strings: format without turning numbers into text
• FORMAT vs format strings vs display units
• Best practices and performance
• Conclusion and next steps
• FAQ

What the DAX FORMAT function does

FORMAT takes one value (a number, a date, a time or a true/false value) and returns a string that looks exactly the way you describe it. You describe the look with a format string such as "#,0.00", "0.0%" or "dd mmm yyyy". You can use it in measures, calculated columns, calculated tables and visual calculations.

It solves a common problem: you need a number inside a sentence. Power BI already formats numbers nicely in a table or a card. But once you join a value to text with &, that formatting is gone. "Sales: 4904.62" looks raw; "Sales: $4,905" looks finished. FORMAT is how you get the second version.

When to use FORMAT

• Dynamic titles and subtitles ("Sales to 28 Mar 2025: $4,905").
• Labels that mix text and numbers, like "▲ 12.4% vs last year".
• Tooltips, text boxes and the HTML or SVG measures you build for custom cards.
• Calculated columns that hold a display label, like "Mar 2025" or "Q1 2025".
• Exports where a downstream system needs a fixed text layout, like "2025-03-07" or "00042".

When not to use it

Don't wrap the measure you put on a chart's Y-axis, a KPI target or a value you still need to sum or compare. Microsoft says it plainly in the FORMAT function reference: a measure that returns FORMAT's text can't be used where a visual needs a number, like charts. For those cases you want a format string on the measure, which we cover later.

How Power BI DAX FORMAT works: syntax and rules

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

• value: any expression that returns a single value, such as a column in row context, a measure or a literal.
• format_string: a named format ("Currency", "Short Date") or a custom pattern ("#,0.0", "mmm yyyy").
• locale_name (optional): a locale such as "en-US", "en-GB" or "de-DE" that controls separators, currency symbols and month names.

Five rules that explain most surprises

1. The result is always text. Even FORMAT(5, "0") returns the string "5".
2. BLANK becomes an empty string. FORMAT(BLANK(), "0") returns "", which is not blank. This one change makes hidden rows appear in tables.
3. A blank format string uses the general format. FORMAT(value, BLANK()) gives "General Number" for numbers and "General Date" for dates.
4. The codes come from Visual Basic, not .NET or Excel. "p" is not accepted as a shortcut for percent, and minutes are "nn".
5. Unknown characters are shown as text or treated as codes. A letter like "d", "m", "q" or "y" means something to FORMAT, so put literal text in double quotes or after a backslash.

Writing quotes inside a format string

A DAX string is wrapped in double quotes, so a double quote inside it is written twice. Both of these lines return "Year 2025":

FORMAT ( DATE ( 2025, 3, 7 ), """Year"" yyyy" )
FORMAT ( DATE ( 2025, 3, 7 ), "\Y\e\a\r yyyy" )

The backslash shows the next character as is. For one or two letters (a "Q" before the quarter, a "K" after thousands) the backslash is shorter and easier to read.

DAX format number: predefined and custom codes

You can describe numbers with a named format or build your own pattern from a few placeholder characters. The table below uses 1234567.891 and the en-US locale so you can see the output of each one.

Format string	Output	What it does
"General Number"	1234567.891	No separators, no rounding
"Fixed"	1234567.89	Two decimals, no separators
"Standard"	1,234,567.89	Two decimals with thousand separators
"Currency"	$1,234,567.89	Locale currency symbol, two decimals
"Scientific"	1.23E+06	Scientific notation
"#,0"	1,234,568	Thousand separators, rounded to a whole number
"#,0.00"	1,234,567.89	Thousand separators, two decimals
"0"	1234568	Whole number, no separators
"#,0,\K"	1,235K	Divided by 1,000 with a K
"#,0.0,,\M"	1.2M	Divided by 1,000,000 with an M
"00000000"	01234568	Leading zeros up to 8 digits

The named formats "Yes/No", "True/False" and "On/Off" also exist: they return No, False or Off for 0 and Yes, True or On for anything else. They are handy for flag columns stored as 0 and 1.

The placeholder characters

• 0 shows a digit or a zero. "000" turns 7 into "007".
• # shows a digit or nothing. "#.##" turns 7.5 into "7.5", not "7.50".
• . is the decimal point; the number of placeholders after it sets the decimals and the rounding.
• , between placeholders adds thousand separators; after the last digit placeholder, each comma divides by 1,000.
• % multiplies by 100 and adds the percent sign.
• \ or "text" shows literal characters.

DAX format thousands separator and decimals

For most report numbers, "#,0" (whole numbers) and "#,0.00" (two decimals) are all you need. You will also see "#,##0.00" in many blogs; it gives the same output, because the extra # signs only matter for leading digits.

Units Label = "Units sold: " & FORMAT ( SUM ( Sales[Quantity] ), "#,0" )
Avg Price Label = "Average price: " & FORMAT ( [Avg Price], "#,0.00" )

FORMAT rounds to the number of decimal places you ask for, so 2.345 with "0.00" shows as 2.35. Your real value in the model is not changed; only the text is.

DAX format currency

The named "Currency" format picks the symbol, separators and decimals from the locale. If your report always shows one currency, a custom pattern gives you more control, such as no decimals on big totals:

Revenue Label = FORMAT ( [Total Sales], "$#,0" )              -- $4,905
Revenue GBP   = FORMAT ( [Total Sales], "£#,0.00" )           -- £4,904.62
Revenue Local = FORMAT ( [Total Sales], "Currency", "en-GB" ) -- £4,904.62

Negative numbers in finance reports are often shown in brackets. A format string can have up to three sections separated by semicolons: positive; negative; zero.

Profit Label = FORMAT ( [Profit], "$#,0;($#,0);-" )
-- 1500  -> $1,500
-- -1500 -> ($1,500)
-- 0     -> -

DAX format percentage

Store percentages as decimals (0.256, not 25.6) and let the format multiply by 100. The named "Percent" format always shows two decimals, so most people prefer a custom pattern:

Margin Label = FORMAT ( [Margin %], "0.0%" )   -- 0.256 -> 25.6%
Margin Whole = FORMAT ( [Margin %], "0%" )     -- 0.256 -> 26%
Growth Sign  = FORMAT ( [YoY %], "+0.0%;-0.0%;0.0%" )   -- 0.124 -> +12.4%, -0.05 -> -5.0%

If your source already holds 25.6 as a whole number, divide by 100 first or use "0.0\%", which shows a literal percent sign without multiplying.

Format number in millions (K, M, B)

Each comma placed after the last digit placeholder divides the number by 1,000 before it is shown. One comma gives thousands, two give millions, three give billions. Escape the letter with a backslash so FORMAT does not read "M" as a month code.

Sales K = FORMAT ( [Total Sales], "#,0.0,\K" )     -- 48,250 -> 48.3K
Sales M = FORMAT ( [Total Sales], "#,0.0,,\M" )    -- 3,480,000 -> 3.5M
Sales B = FORMAT ( [Total Sales], "#,0.0,,,\B" )   -- 2,100,000,000 -> 2.1B

To pick the unit automatically, test the size of the value first. The ABS keeps negative numbers on the right unit, and the ISBLANK check stops empty rows from appearing (more on that in the errors section).

Sales Short =
VAR v = [Total Sales]
VAR a = ABS ( v )
RETURN
    IF (
        ISBLANK ( v ),
        BLANK (),
        SWITCH (
            TRUE (),
            a >= 1000000000, FORMAT ( v, "#,0.0,,,\B" ),
            a >= 1000000, FORMAT ( v, "#,0.0,,\M" ),
            a >= 1000, FORMAT ( v, "#,0.0,\K" ),
            FORMAT ( v, "#,0" )
        )
    )

This version is text, so keep it for labels and tooltips. The same logic as a dynamic format string (later in this guide) works on charts too.

DAX format date: every code with examples

Date codes describe each part of a date: day, month, year, quarter, week and time. The table uses Friday, 7 March 2025 at 14:05:09 with the en-US locale.

Code	Output	Meaning
"d"	7	Day, no leading zero
"dd"	07	Day with leading zero
"ddd"	Fri	Short weekday name
"dddd"	Friday	Full weekday name
"m"	3	Month number, no leading zero
"mm"	03	Month number with leading zero
"mmm"	Mar	Short month name
"mmmm"	March	Full month name
"yy"	25	Two-digit year
"yyyy"	2025	Four-digit year
"q"	1	Quarter number
"w"	6	Day of the week (1 = Sunday)
"ww"	10	Week of the year (weeks start on Sunday)
"y"	66	Day of the year (not the year!)
"\Qq yyyy"	Q1 2025	Quarter label
"yyyy-mm-dd"	2025-03-07	ISO-style date for exports
"dd mmm yyyy"	07 Mar 2025	Clear in every country
"dddd, mmmm d, yyyy"	Friday, March 7, 2025	Long US style
"Short Date"	3/7/2025	Locale short date
"Long Date"	Friday, March 7, 2025	Locale long date

The Microsoft Learn reference writes these codes in lowercase, and that is the safest habit. Keep in mind that "y" on its own is the day of the year: "d/m/y" will not give you the year.

Power BI convert date to month and year

This is the most common date job: turning a date into "Mar 2025" for a matrix header or an axis label. In your Date table, add a calculated column:

Month Year = FORMAT ( 'Date'[Date], "mmm yyyy" )      -- Mar 2025
Month Year Short = FORMAT ( 'Date'[Date], "mmm-yy" )  -- Mar-25
Year Month = FORMAT ( 'Date'[Date], "yyyy-mm" )       -- 2025-03

"yyyy-mm" sorts correctly on its own because the year comes first. "mmm yyyy" reads better but sorts alphabetically, so it needs a sort column. The fix is in the errors section below.

Quarters, weeks and weekday names

Quarter Label = FORMAT ( 'Date'[Date], "\Qq yyyy" )    -- Q1 2025
Weekday Short = FORMAT ( 'Date'[Date], "ddd" )         -- Fri
Week Label    = "W" & FORMAT ( 'Date'[Date], "ww" )    -- W10

The "ww" code counts weeks from Sunday and starts week 1 on 1 January. If your business uses ISO weeks (Monday start, week 1 holds the first Thursday), use WEEKNUM('Date'[Date], 21) instead and format that number.

DAX format time

Time codes work the same way. The trap is minutes: "m" and "mm" mean minutes only when they come straight after "h" or "hh". Anywhere else they mean the month. Use "n" and "nn" for minutes and you never have to think about it.

Time 24h  = FORMAT ( Sales[OrderTime], "hh:nn" )          -- 14:05
Time 12h  = FORMAT ( Sales[OrderTime], "h:nn AM/PM" )     -- 2:05 PM
Time Full = FORMAT ( Sales[OrderTime], "hh:nn:ss" )       -- 14:05:09
Stamp     = FORMAT ( Sales[OrderDateTime], "dd mmm yyyy hh:nn" )  -- 07 Mar 2025 14:05

Format for any country with the locale argument

Without the third argument, named formats such as "Currency" or "Short Date" use the model's culture. In Power BI Desktop that comes from the language setting of the file (File, Options and settings, Options, Regional settings). So the same report can look different on a colleague's machine, which is why the locale argument is worth knowing.

Expression	Output
FORMAT ( 1234567.5, "Currency", "en-US" )	$1,234,567.50
FORMAT ( 1234567.5, "Currency", "en-GB" )	£1,234,567.50
FORMAT ( 1234567.5, "Standard", "de-DE" )	1.234.567,50
FORMAT ( 1234567.5, "Currency", "en-IN" )	₹12,34,567.50
FORMAT ( DATE(2025,3,7), "mmmm", "fr-FR" )	mars
FORMAT ( DATE(2025,3,7), "Short Date", "en-GB" )	07/03/2025

Named formats adapt fully to the locale. In custom strings the codes stay fixed, so "mm/dd/yyyy" keeps month first everywhere, but the separator characters (/ , .) and the month and day names still follow the locale. If you want one fixed look for every user, use a custom string like "dd mmm yyyy" and pass a locale.

You can even let users pick. Put the locale codes in a small table with a slicer and read the selection:

Sales Local =
VAR loc = SELECTEDVALUE ( Locales[Code], "en-US" )
VAR v = [Total Sales]
RETURN
    IF ( ISBLANK ( v ), BLANK (), FORMAT ( v, "Currency", loc ) )

This only changes the symbol and separators. It does not convert the amount; for real currency conversion you need an exchange rate table.

Worked example: a dynamic card title and a growth label

Here is a small model you can rebuild in two minutes. Create a table called Sales with Enter data and this sample data, plus a Date table marked as a date table:

OrderDate	Product	Amount
15 Jan 2025	Laptop	1,250.40
3 Feb 2025	Monitor	389.99
20 Feb 2025	Laptop	2,410.00
8 Mar 2025	Keyboard	74.25
28 Mar 2025	Monitor	779.98

The goal: a card title that always says what period the number covers, and a growth label for the subtitle. Follow these steps:

1. Create the base measure: Total Sales = SUM ( Sales[Amount] ). Set its format to Currency in Measure tools so the card itself stays numeric.
2. Create the title measure below. It finds the last order date in the current filter and formats both values.
3. Add a card with Total Sales. In the Format pane, open General, then Title, and select the fx button next to the title text.
4. Choose Format style "Field value" and pick the Sales Title measure. Select OK.
5. Repeat for the subtitle with the YoY Label measure.

Sales Title =
VAR LastDate = MAX ( Sales[OrderDate] )
VAR Total = [Total Sales]
RETURN
    IF (
        ISBLANK ( Total ),
        "No sales in this selection",
        "Sales to " & FORMAT ( LastDate, "d mmm yyyy" ) & ": " & FORMAT ( Total, "$#,0" )
    )

With no filter, the total is 1,250.40 + 389.99 + 2,410.00 + 74.25 + 779.98 = 4,904.62, and the last date is 28 March. The title reads "Sales to 28 Mar 2025: $4,905". Filter to Laptop and it changes to "Sales to 20 Feb 2025: $3,660".

YoY Label =
VAR Curr = [Total Sales]
VAR Prev = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR Growth = DIVIDE ( Curr - Prev, Prev )
RETURN
    IF (
        ISBLANK ( Growth ),
        "No prior-year data",
        IF ( Growth >= 0, "▲ ", "▼ " ) & FORMAT ( ABS ( Growth ), "0.0%" ) & " vs last year"
    )

DIVIDE returns BLANK when last year is empty, so the label says so instead of showing an error or "∞". The arrow is added with IF and the number uses ABS, so you never get "▼ -5.0%". If you want the label coloured too, add conditional formatting on the subtitle with a second measure that returns a colour code.

Common FORMAT errors and how to fix them

These are the problems people hit most with FORMAT, taken from the questions that come up again and again on the Fabric community forum. Each one has a short fix.

Format causing measure to return blank rows (or every row)

You add a FORMAT measure to a table and suddenly every product or every date appears, even ones with no sales. The reason: FORMAT(BLANK()) returns an empty string, not BLANK. Power BI hides rows where all measures are blank, and "" is not blank, so nothing gets hidden.

-- Shows every row in the table
Sales Text Bad = FORMAT ( [Total Sales], "$#,0" )

-- Shows only rows with sales
Sales Text =
VAR v = [Total Sales]
RETURN
    IF ( ISBLANK ( v ), BLANK (), FORMAT ( v, "$#,0" ) )

The VAR also means [Total Sales] is calculated once instead of twice. Some forum answers suggest adding "+ 0" to the measure; that removes the empty rows from a card but turns real blanks into "$0", which is usually wrong in a table.

The chart won't accept the measure

A measure that returns FORMAT's text can't go into the Y-axis of a bar or line chart, and a card shows it as text. Keep the numeric measure for the chart and give it a format string, or use a dynamic format string if the look must change. Use the FORMAT version only in titles and tooltips.

Months sort alphabetically (Apr, Aug, Dec...)

Text sorts A to Z, so "mmm yyyy" puts April before January. Add a numeric sort column and tell Power BI to use it:

Month Year Sort = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] )   -- 202503

1. Select the Month Year column in the Data pane.
2. On the Column tools ribbon, select Sort by column.
3. Choose Month Year Sort. Visuals now sort Jan 2025, Feb 2025, Mar 2025.

Minutes show the month (or the year shows a big number)

"hh:mm" works, but "mm:ss" on its own may show the month, because "mm" means minutes only right after an hour code. Use "nn" for minutes. If a date shows "66" where you expected 2025, you wrote "y" instead of "yyyy".

Wrong separators or currency on another machine

Named formats follow the model's language and the user's locale. If a colleague in Germany sees "1.234,50" where you see "1,234.50", pass a locale as the third argument, or set the model language under Options, Regional settings.

FORMAT inside SUMX or another calculation fails

Iterators like SUMX need numbers, and FORMAT returns text, so SUMX(Sales, FORMAT(Sales[Amount], "0")) produces an error or nonsense. Do the maths first and format the final result once, at the very end of the measure.

Not supported in DirectQuery columns and RLS

FORMAT can't be used in calculated columns or row-level security rules on a DirectQuery table. Do the formatting in the source view, in a measure, or with a format string on the column.

Dynamic format strings: format without turning numbers into text

Dynamic format strings are Power BI's answer to FORMAT's biggest weakness. You write a DAX expression that returns a format string, and Power BI applies it to the measure while the value stays a number. Charts, sorting, totals and conditional formatting all keep working. Microsoft documents the feature in Create dynamic format strings for measures.

How to set one up

1. In the Data pane, select the measure (for example, Total Sales).
2. On the Measure tools ribbon, in the Formatting group, open the Format list and choose Dynamic.
3. A drop-down appears left of the formula bar, set to Format. The formula bar now holds the format string expression instead of the measure.
4. Replace the text with a DAX expression that returns a format string, like the one below, and press Enter.
5. In each visual that shows the measure, set Display units to None, or Power BI may scale the value a second time.

VAR a = ABS ( SELECTEDMEASURE () )
RETURN
    SWITCH (
        TRUE (),
        a >= 1000000000, "$#,0.0,,,\B",
        a >= 1000000, "$#,0.0,,\M",
        a >= 1000, "$#,0.0,\K",
        "$#,0"
    )

Now the same measure shows $2.1B on a country total, $3.5M on a region and $48.3K on a single store, and the bar chart still draws bars of the right length. SELECTEDMEASURE() returns the measure's own value; you can also write the measure name, like [Total Sales].

Format a measure by a slicer choice

Dynamic format strings can also read slicers. With a small table of country names and format strings, one measure can show "€ 1,234.00" for the Euro Zone and "US$1,234.00" for the United States:

SELECTEDVALUE ( 'Currency Formats'[Format], "$#,0.00" )

Limits to know

• They work on model measures only, not on report measures in a live connection.
• Combo charts in "Show as table" view don't use the dynamic format string.
• To format many measures with one rule, use a calculation group; the same SELECTEDMEASURE patterns apply there.

FORMAT vs format strings vs display units

Power BI gives you four ways to control how a number looks. Picking the right one is most of the skill, so here they are side by side.

Method	Result type	Works in charts	Changes by context	Best for
FORMAT function	Text	No	Yes (any DAX logic)	Titles, labels, tooltips, text joined to values
Static format string (Measure or Column tools)	Number or date	Yes	No	Most measures and columns, one fixed look
Dynamic format string	Number	Yes	Yes (DAX returns the format)	K/M/B auto units, currency by slicer, durations
Visual display units (Format pane)	Number	Yes	Auto only	Quick thousands or millions on one visual

My rule of thumb: start with a static format string, move to a dynamic format string when the look must change, and use FORMAT only when the number has to live inside text. That order keeps your model fast and your charts working.

Custom number formatting in Power BI without DAX

Select a measure or numeric column, then in Measure tools or Column tools pick a format from the Format list, or type your own pattern in the box. The box accepts the same codes as FORMAT, such as #,0.0,,\M or 0.0%, but the value stays numeric. This is the right place for 80% of formatting work.

How to change date format in Power BI without DAX

Select the date column in the Data pane, open Column tools, and choose a format from the Format list, or type a custom one such as dd mmm yyyy. Every visual that uses the column picks it up and the column still sorts and filters as a real date. Use a FORMAT calculated column only when you need a separate label like "Mar 2025".

Best practices and performance

FORMAT is cheap on a card and expensive on a big table. Every cell that calls it builds a string, so a table visual with thousands of rows of FORMAT measures takes longer to render than the same table with numbers and a format string.

• Format last. Do the maths with numbers, store the result in a VAR, and call FORMAT once on the final value.
• Always guard blanks. Use IF(ISBLANK(v), BLANK(), FORMAT(v, ...)) unless you really want every row.
• Keep a numeric twin. For every text measure, keep the numeric measure it comes from for charts, sorting and conditional formatting.
• Prefer measures to columns for labels. A formatted calculated column with many unique values adds size to the model. Month labels are fine; formatting every transaction amount is not.
• Use unambiguous dates. "07 Mar 2025" can't be misread in any country; "03/07/2025" can.
• Name text measures clearly. A suffix like "Label" or "Text" tells report builders not to drop it into a chart.
• Keep the look consistent. Pick one number style per unit (K for thousands, M for millions, one decimal) and use it everywhere.

Design tips for formatted labels

Text from FORMAT takes the font of the visual it sits in, so set fonts, sizes and colours once in a report theme instead of fixing each card. Our free Power BI theme builder lets you choose your title font, card text sizes and palette and export the theme JSON in a few minutes. If you want a starting point with dynamic titles already wired up, the Power BI templates in our marketplace show these patterns in finished dashboards.

Conclusion and next steps

The Power BI DAX FORMAT function is the right tool whenever a number or date has to appear inside text: titles, subtitles, tooltips and custom labels. Remember that it always returns text, so guard blanks with ISBLANK, sort text months with a sort column, and never put a FORMAT measure on a chart axis.

When the value itself must stay numeric, use a static format string or a dynamic format string instead. Your next step: open a report you own, find one card title that still says "Total Sales", and replace it with a Sales Title measure from the worked example. Then browse our Power BI tutorials for more DAX patterns.

FAQ

How do I format a number in DAX?

Use FORMAT with a pattern, for example FORMAT([Total Sales], "#,0.00") for thousand separators and two decimals. If the number goes on a chart, set the format in Measure tools instead so it stays numeric.

How do I format a date in a Power BI measure?

Wrap the date in FORMAT with date codes, such as FORMAT(MAX('Date'[Date]), "dd mmm yyyy") for "07 Mar 2025". Use "mmm yyyy" for month and year and "\Qq yyyy" for a quarter label.

Why does my FORMAT measure show blank rows in a table?

FORMAT turns BLANK into an empty string, and Power BI only hides rows where measures are truly blank. Wrap it like IF(ISBLANK(v), BLANK(), FORMAT(v, "#,0")) and the empty rows disappear.

Why can't I use a FORMAT measure in a chart?

FORMAT always returns text, and chart values need numbers. Keep the numeric measure on the chart and use a format string or a dynamic format string to control how it looks.

How do I show numbers in millions with DAX?

Add two commas after the last digit placeholder: FORMAT([Total Sales], "#,0.0,,\M") shows 3,480,000 as 3.5M. For charts, put the same pattern in a dynamic format string and set Display units to None.

How do I format a percentage in DAX?

Store the value as a decimal and use FORMAT([Margin %], "0.0%"), which turns 0.256 into 25.6%. Use "0%" for whole percentages, or "+0.0%;-0.0%" to always show the sign.

What is the difference between FORMAT and dynamic format strings?

FORMAT changes the value into text, so it can't be summed, sorted as a number or charted. A dynamic format string only changes how the measure is displayed, and the value stays numeric.

Why does FORMAT show the month instead of minutes?

"mm" means minutes only when it comes straight after "h" or "hh"; anywhere else it is the month. Use "nn" for minutes, as in "hh:nn", and the problem goes away.