Power BI offers several ways to change date formats. Users can change date formats using the Power Query Editor, directly in the report view screen, or with custom DAX functions. We’ll explain how and when to use each one of these methods to format dates to the way that you need them.
We’ll also explain some of the problems that you are likely to run into while working with dates and how to fix them when they arise.
Let’s dive in!
Table of Contents
The Different Methods for Updating Dates in Power BI
Changing date formats in Power BI can be done in several different ways. You can change date formats at the data model level using the Power Query editor, they can also be updated at the report or visual level by defining a custom format using a DAX formula.
Power BI executes date formatting changes in a hierarchy. When changes are made using the Power Query Editor, it becomes the baseline format that all visualizations in the report view will start with. Changes at the report level will override the format established in Power Query.
Changing Date Formats in the Power BI Report View
To quickly change the date format of a field in Power BI, select the date field from the data panel. Then navigate to the Column Tools section of the Power BI Ribbon. Use the Format drop down menu to select one of the dozens of available pre-formatted date options.
The screenshot below shows a Date field selected on the Data Panel to the right, and the format dropdown Column Tools section at the top of the page in the Power BI Ribbon.
The dropdown meu include dozens of potential options for date formats, including the most common styles used in the United States. Try matching the date format to the size of the visualization you are presenting. Dates formatted with the full spelling of month, or day may be difficult to fit on visuals with multiple elements.
Note: If the format option is viewable under Column Tools, but is greyed out it may be that the report is using data from a live connection or direct query mode. You may have to create a custom measure that includes the date or change the data model to import mode.
Updating Date Formats in Power Query Editor
To modify the format of a date using the Power Query Editor, right click on the column you wish to update, go to Change Type, then select Date/Time, Date, Date/Time/Timezone or one of the other options available. For more region standards, there is a “Using Locale” option to convert date formats into a regional preset.
More advanced formatting options are available in the report view of Power BI. The Power Query editor is primarily meant to give report builds a starting point where all dates are in a standard format that can be formatted further when building a dashboard.
Creating Custom Date Formats in Power Query
When starting with a standard date format, you can use the Date Button to quickly extract parts of a date column, and then concatenate them into a column with a custom format. The final column will be a text data type and may not be suitable for sorting in chronological order but can be used for displaying dates.
To create a custom date format, select the date column, and navigate to the Date button on the Add Column section of the Power Query Ribbon.
The Date button will generate new columns to the right of the dataset. An individual column is created each time you use the Add Date Column button. By splitting the date into pieces we can then put it back together using a custom column and concatenation.
Once your data has been split into sections, click on Custom Column from the Add Column section of the Power Query Editor.
This launches the Custom Column editor. Double click the names of the columns on the right side to insert the name surrounded by [ ] brackets.
Use the & symbol to concatenate values. Anything listed in Quotes can also be concatenated like the example below.
The final results from our example will look like the example below. The format of Month – YYYY was created by splitting a date column into smaller pieces then adding it back together in a custom column.
Note: Columns must be defined as a Text Data Type for Power Query to be able to concatenate them. Right click on a column name and change the data type to text prior to creating a custom column to avoid formatting errors.
Using DAX Formulas to Customize Power BI Date Formats
Rather than editing date formats using the Power Query Editor, Power BI developers can use DAX formulas to assign custom formatting to dates for use in reports or specific visuals. To change the formatting, create a New Column in the Power BI Report View, and use the DAX formula FORMAT.
The DAX FORMAT formula uses 3 arguments.
The name of the table and field being updated , the desired format , the locale (optional)
The following example creates a new column based on the Date Field from the Trial Balance table and results in a new column that looks like the example below it.
Date = FORMAT(‘Trial Balance'[Date],”YYYY-MM”)
Here is some helpful coding that will let you quickly produce DAX formulas that result in different lengths of years, months, days etc. This table isn’t exhaustive as there are a number of other formats that also let you adjust how minutes and time measurements are presented.
| Format String | Description | Example Input | Example Output |
|---|---|---|---|
"YYYY" | Four-digit year | 2023-03-15 | 2023 |
"YY" | Two-digit year | 2023-03-15 | 23 |
"MM" | Two-digit month | 2023-03-15 | 03 |
"MMM" | Abbreviated month name | 2023-03-15 | Mar |
"MMMM" | Full month name | 2023-03-15 | March |
"DD" | Two-digit day | 2023-03-15 | 15 |
"DDD" | Abbreviated day name | 2023-03-15 | Wed |
"DDDD" | Full day name | 2023-03-15 | Wednesday |
While these transformations can also be done in Power Query, they are much easier to do in DAX. Just remember to create a New Column instead of a New Measure as dates have to be evaluated one at a time. Measures are used to aggregate values, which wouldn’t make sense for a list of dates.
Troubleshooting Date Formatting in Power BI
Here are some common issues that you may run into when working with date formats and how to address them.
Dates Formatting Options Greyed Out
There are two reasons that you may not have date formatting options available in the report view of Power BI. The first is if you see the format options greyed out. This is typically a sign of a direct query or live connection. In these cases, the data isn’t being imported into Power BI and cannot be adjusted.
As a workaround, you can create a custom measure that uses that field as a source. Then you can change the formatting options of the new measure. You can also switch the data model to import mode. Be aware that not all models will support this, and it may not be practical to import a full dataset.
Date Formatting Options Not Available / Not Applicable
If you go to update the formatting of a date column, but the format options are related to numbers or text it’s likely that you need to update the field Data Type. Switch the Data Type on the left side of the Column Tools Ribbon. The data type will drive which formatting options are available.
Unable to Convert Strings to Date Format
Sometimes dates are imported into a data model using a non-standard format. While Power BI is pretty good at converting common date formats into a standard date format where you can apply a date data type it’s not always possible. In these cases, use the Power Query Editor to parse, and extract parts of the date like the month and year then concatenate it into a standard format that Power BI can interpret.
Adjusting Date Formats for Geographic Locations
Geographic locales define how dates are presented either at the file, or global level. To adjust them, you can go to File, Options, Regional Settings in Power BI Desktop and assign a specific locale. This will adjust all of the representations of dates in Power BI to that locale’s standard.
Conclusion
Formatting dates in Power BI can be done in a number of different ways. The timing of where you update the formatting will largely be driven by how the date fields are going to be used and if they need to be overridden. Date formats defined in Power Query can be overridden and defined at the repot view. To define different date formats at the visual level you will need to create custom measures that use the desired format.
The fastest way to update a date format in Power BI is by clicking on the date in the Data panel and selecting a new format under Column Tools. This option isn’t always available under certain data connection modes. To override it and gain more control over custom formatting, create a New Column using DAX formulas.