The FORMAT function is a gem when it comes to applying custom formats to your Calculations. To begin with I’ll share with you it’s bare syntax and then some fun ways you can use the FORMAT Function.
A Quick Video!
FORMAT Function Syntax
= FORMAT ( <value>, <format_string> )
- Value – Can be a Text or a Number
- Format String – Can be any of the various strings (available) to format the value in the desired way.
Quick Example, the following format function will result in 1000.4 (with one decimal), you get the point!
= FORMAT(1000.44, "0.0")
Now that we’ve slayed the Syntax, let’s play with a few interesting examples
Using Format with Numbers
The interesting part to watch out for is the strings used (highlighted in red) and the Result produced
| Format Function | Result | What it Does |
FORMAT(1000,"0,0")
|
1,000 | Adds a comma separator as per your system settings |
FORMAT(1000,"0,0.0")
|
1,000.0 | Adds a commas and decimals |
FORMAT(1000,"0.0;(0.0);-")
|
1000.0 | Positive number – Appears in one decimal place |
FORMAT(-1000,"0.0;(0.0);-")
|
(1000.0) | Negative Number – Appears in brackets with one decimal |
FORMAT(0,"0.0;(0.0);-")
|
– | Zero – appears as a dash |
FORMAT(1000,"Currency")
|
$1,000.00 | Appears in the Currency Format as per system settings |
FORMAT(1000, "0 ✔️;(0) ❌") FORMAT(-1000, "0 ✔️;(0) ❌") |
1000 ✔️
(1000) ❌ |
Positive Number appears with a ✔️
Negative Number appears with ❌ |
Using Format with Dates
Again watch out for the strings used (highlighted in red) and the Result produced
| Format Function | Result | What it Does |
FORMAT(DATE(2020,1,20)+.20, "c")
|
1/20/2020 4:48:00 AM | Display the date as ddddd and display the time as ttttt, in that order |
FORMAT(DATE(2020,1,20), "dddd")
|
Monday | Display the day as a full name (Sunday-Saturday) |
FORMAT(DATE(2020,1,20),"w")
|
2 | Display the day of the week as a number (1 for Sunday through 7 for Saturday) |
FORMAT(DATE(2020,1,20), "ww")
|
4 | Display the week of the year as a number (1-54) |
FORMAT(DATE(2020,1,20), "mmmm")
|
January | Display the month as a full month name (January-December) |
FORMAT(DATE(2020,1,20), "q")
|
1 | Display the quarter of the year as a number (1-4) |
FORMAT(DATE(2020,1,20), "\Qq")
|
Q1 | Prefixes a Q at the start of quarter number |
FORMAT(DATE(2020,1,20), "yyyy \Qq")
|
2020 Q1 | Displays Year and Quarter Number |
FORMAT(DATE(2020,1,20), "ttttt")
|
12:00:00 AM | Display a time as a complete time (including hour, minute, and second) |
Using Format with Booleans
And one last time again watch out for the strings used (highlighted in red) and the Result produced
| Format Function | Result | What it Does |
FORMAT(0, "On/Off")
FORMAT(1, "On/Off")
|
Off
On |
Displays Off if number is 0; otherwise, displays On |
FORMAT(0, "True/False")
FORMAT(1, "True/False")
|
FALSE
TRUE |
Displays False if number is 0; otherwise, displays True |
FORMAT(0, "Yes/No")
FORMAT(1, "Yes/No")
|
No
Yes |
Displays No if number is 0; otherwise, displays Yes |
I am sure I have missed out a bunch of FORMAT function tricks that YOU know and I don’t. Be a gentleman and drop in your knowledge bomb in the comments. I’ll be so happy 😃
Learn DAX Functions
Check out my Video series for DAX Functions
More on Format from Microsoft
More on DAX
- Calculate Function Tricks in Power BI
- Understanding EARLIER Function
- Change Measure using a Slicer
- Calculate MAX of Multiple Columns
- Calculate Fiscal Week in Power BI
- Sort by Column – Interesting Examples
Learn DAX from Scratch !!!
A comprehensive course to learn DAX step by step and solve real time problems in Power BI.
DOWNLOAD THE COURSE OUTLINE | ENROLL IN THE COURSE
