Graphed LogoGraphed
Login

How to Format Date in Table in Power BI

Cody Schneider•

Working with dates in Power BI tables can feel surprisingly complicated, but getting the format right is essential for creating reports that are easy to read and understand. Whether you need to switch from the US format (MM/DD/YYYY) to an international one (DD/MM/YYYY), or just display the month and year, there are several simple ways to do it. This tutorial will walk you through the most common methods for formatting dates in Power BI, using both the simple user interface and more powerful DAX functions.

Why Does Date Formatting Matter in Your Reports?

Before diving into the "how," it's worth understanding "why" this small detail is so important. Proper date formatting isn't just about making things look nice, it directly impacts the usability and clarity of your data table.

  • Improves Readability: Displaying January 15, 2024 is far more readable than 1/15/2024 12:00:00 AM. Your audience shouldn’t have to decipher messy timestamps.

  • Ensures Consistency: When pulling data from multiple sources, dates can come in all sorts of formats. Standardizing them gives your reports a professional and consistent feel.

  • Prevents Sorting Chaos: If you format a date as a text string (like "April 2024"), it might sort alphabetically (April, August, December) instead of chronologically. We'll cover how to fix this later on.

  • Matches Audience Expectations: Users in the UK expect to see 15/01/2024, while users in the US anticipate 01/15/2024. Formatting for your specific audience avoids confusion.

Two Main Ways to Format Dates in Power BI

You have two primary options for formatting dates in a data table, and the one you choose depends on your goal:

  1. Using the Column Tools Menu: This is the quickest and easiest method, perfect for applying standard or popular date formats directly to an existing column. It’s a point-and-click solution that requires no code.

  2. Using DAX (Data Analysis Expressions): This method involves writing a simple formula to create a new column with the desired format. It offers unlimited flexibility for creating custom date formats that aren't available in the standard menu.

Let's walk through both approaches step-by-step.

Method 1: Formatting Dates Using the Column Tools Ribbon

This is the go-to method for quick and easy formatting. Let's say you have a table with a column named 'OrderDate' that currently looks jagged and includes a redundant timestamp.

Here’s how to clean it up.

Step-by-Step Instructions:

  1. Go to the Data View: Once your data is loaded into Power BI, navigate to the Data View by clicking the table icon on the left-hand sidebar.

  2. Select Your Date Column: In the 'Data' pane on the right, find your table and click on the date column you want to format (e.g., 'OrderDate'). This will highlight the entire column in your table.

  3. Open the Column Tools: When you select the column, a new contextual tab called Column tools will appear in the top ribbon. Click on it.

  4. Apply a New Format: In the "Formatting" section of the ribbon, you'll see a dropdown menu next to "Format". Click it to reveal a list of predefined date and time formats. You can select common options like:

    • dd MMMM yyyy (e.g., 15 January 2024)

    • MM/dd/yyyy (e.g., 01/15/2024)

    • dddd, MMMM d, yyyy (e.g., Monday, January 15, 2024)

    • Short Date (uses your system's regional settings)

Select your desired format, and your column will immediately update. It's that simple!

Method 2: Creating Custom Date Formats with DAX

What if the format you need isn’t in the predefined list? For example, perhaps you want to display just the month and year, like "Jan-24", or create a custom quarter label like "2024-Q1". This is where DAX and its powerful FORMAT function come into play.

Using DAX allows you to create a new, separate column, leaving your original date column untouched. This is best practice, as it preserves the original data for calculations while giving you a perfectly formatted text column for display purposes.

The FORMAT Function Explained

The DAX FORMAT function is very simple. It takes two arguments:

  • The value you want to format (your date column).

  • The format string you want to apply.

New Column Name = FORMAT([YourDateColumn], "Your Format String")

Step-by-Step Instructions:

  1. Stay in the Data View: Remaining in the Data View, select the table you want to add the new column to.

  2. Create a New Column: From the Home tab in the ribbon, click New column. A formula bar will appear at the top, just like in Excel.

  3. Write Your DAX Formula: Now, type in your formula using the FORMAT function. Let's look at a few common examples.

Example 1: Show Month Name and Year (e.g., January 2024)

In the formula bar, type the following DAX expression:

Hit Enter. A new "Formatted Month-Year" column will appear in your table.

Example 2: Show Abbreviated Month and Day (e.g., Jan 15)

Use this DAX pattern:

Example 3: Create a Fiscal Year Quarter (e.g., FY24-Q1)

Here you can combine the format function with other text strings:

Common Custom Format Codes

The real power of the FORMAT function comes from knowing the text codes. Here is a handy reference table:

  • For Year:

    • yyyy - Four-digit year (e.g., 2024)

    • yy - Two-digit year (e.g., 24)

  • For Month:

    • mmmm - Full month name (e.g., January)

    • mmm - Abbreviated month name (e.g., Jan)

    • mm - Two-digit month (e.g., 01)

    • m - One or two-digit month (e.g., 1)

  • For Day:

    • dddd - Full day name (e.g., Monday)

    • ddd - Abbreviated day name (e.g., Mon)

    • dd - Two-digit day (e.g., 09)

    • d - One or two-digit day (e.g., 9)

  • For Quarter:

    • q - Displays the quarter number (1, 2, 3, or 4)

The Most Important Final Step: Solving an Alphabetical Sort Problem

You’ve created your beautiful new formatted date column using DAX. Let's say you used the Formatted Month-Year example, so you have values like "January 2024", "February 2024", and so on. But when you use this column in a chart or a table visual, you notice a problem... it's sorted alphabetically!

This happens because your new column is technically a text column, not a date column. Power BI doesn't know "February" comes after "January."

How to Fix It with "Sort by Column"

This is a common hurdle for beginners, but the fix is brilliant and simple.

  1. Select Your Formatted Text Column: Go to the Data view and click on the header of the newly created text column (e.g., 'Formatted Month-Year').

  2. Use the "Sort by Column" Feature: In the Column tools ribbon, find and click the Sort by Column button.

  3. Choose the Original Date Column: A dropdown menu will appear. From this list, select the original, unformatted date column in your table (e.g., 'OrderDate').

And that's it! Behind the scenes, Power BI will now use the chronological order of your original 'OrderDate' column to sort the text values in your 'Formatted Month-Year' column. Your visuals will instantly snap into the correct chronological order.

Final Thoughts

Formatting dates correctly is a fundamental skill for creating clean and effective reports in Power BI. Using the Column Tools is perfect for quick, standard formats, while the DAX FORMAT function gives you the control to create any custom date format imaginable. Remember to always use the "Sort by Column" feature to ensure your text-based dates remain in chronological order.

While mastering these Power BI skills is essential, sometimes you need insights without the manual configuration. That's why we built Graphed to simplify the entire analysis and reporting process. Instead of working through menus and formulas, you can connect your data sources and create live dashboards just by describing what you want to see in plain English, helping you get direct answers and share insights in seconds.