Power BI is a business analysis and data modelling service for confident decision-making. By connecting all your data sources at the scale you need, you can visualise and share insights across the whole organisation.
Power BI is based on the principle of Business Intelligence. Many users have difficulty formatting dates in Power BI. Find out how to format the date in Power BI. First, a reminder about the tool and Business Intelligence.
Definition of Business Intelligence
The term Business Intelligence (BI) refers to the technologies, applications, processes and skills used to collect, integrate, analyse and visualise key information for organisations.
By analysing large volumes of data and converting it into usable information, Business Intelligence aims to improve decision-making in the commercial, marketing, financial and managerial sectors.
A variety of data sources are used to create comprehensive tables that facilitate all decision-making processes:
- Customer management software (CRM) ;
- Supply chain information (suppliers);
- Sales performance;
- After-sales service performance;
- Marketing expenditure and results;
- Website data;
- etc.
The benefits of Business Intelligence are crucial in today’s organisations. Its tools can speed up the decision-making process, optimise areas where results fall short of expectations, increase team efficiency, generate more revenue and maintain a competitive edge.
Business Intelligence also has a long-term impact for organisations: it provides an overview of the business over several years, and improves visibility of processes and areas that require particular attention.
The main features of Power BI
With optimum accessibility and visual presentation, Power BI is an effective business intelligence tool from Microsoft for organisations.
It is one of the most widely used tools for analysing and modelling data. Microsoft Power BI is available on computers, smartphones and cloud platforms, enabling company employees to access information and analyse data from anywhere
The Power BI tool can collect data from multiple sources and formats, including Excel, PDF, CSV, SQL Server, MySQL, Access, XML, JSON and many more.
Power BI features include data modelling and a range of visualisation formats. Users can divide voluminous data into several categories, precisely select the possibilities and generate unique tables.
Table creators can also use artificial intelligence (AI) to prepare data, build machine learning models and obtain information from unstructured data. The tool also facilitates the creation of subsets for more detailed analysis of the organisation’s data.
Finally, Power BI makes it easy to share information within the company. Reports can be sent to other team members without having to worry about security breaches. It is also possible to define which teams receive which analyses, offering an additional degree of customisation that enhances the organisation’s overall productivity.
Changing the date format in Power BI
Are you using Power BI but don’t know how to change the date format?
Discover several options and formats for displaying the date within the tool.
To do this:
- Open Power BI on your computer and load your data.
Make sure your data has a column for dates.* - In your Field panel, click on the “Date” column. This will open the tools column allowing you to change the date format in Power BI.
- In the “Format” section, you can change the date format using the drop-down menu.
- These choices include the popular selection dd/mm/yyyy (16-11-2022).
Sometimes the date format changes when the report is published: the days and months are inverted. To solve this problem, change the default language from English US to English UK.
Date format in power BI: mmm-yy
Changing the date format to mmm-yy (example: Jun 22) is not available in the options in Power BI. To obtain this display, use a DAX formula.
- Open Power BI on your computer and load your data.
- Click on the “Modeling” tab and then click on “New column”.
- Enter the following DAX formula: Custom Column = FORMAT (‘Orders'[Order Date], “MMM YY”).
Change date format from yyyymmdd to dd mmm yyyy
The yyyymmdd date format (example: 20220923) is not an optimal display. It’s much nicer to be able to read the date clearly. To achieve this, create a new column with a DAX formula.
- Click on the “Modeling” tab and then on “New column”.
- Enter the DAX formula: dateFormatted =
- Date(Left([Column Date],4),Right(left([Column Date],6),2),right([Column Date],2)).
(You can copy and paste the formula directly into Power BI without the full stop at the end of the sentence).
Date format in Power BI: abbreviation of the month
When you create a visualization table with the name of each month on the x-axis, some months are likely to take up too much space (like September or November, for example).
It is therefore necessary to use the abbreviation of the months to optimise the display of the table.
To do this:
- Click on the “Modeling” tab and then on “New column”.
- Enter the following DAX formula: Short_Name = Format (BIData[Date], “mmm”).
- Now add the “Short_Name(calculated column)” to the x-axis of the table.
- Only the first three letters of each month now appear.
Date format in Power BI: quarter
If you prefer to group dates by quarter in your views, you will need to use a DAX formula again. The date 14/09/2022 will then appear as follows: Qtr 3 2022.
Click on the “Modeling” tab and then on “New column”.
Enter the following DAX formula: Column = BIData[Date].[Quarter] &” ” & BIData[Date].[Year].
Date format in Power BI: am pm
When the time appears in your table, it can take the form of 09:45:00 or 09:45:tt. Let’s see how to change it by adding AM or PM :
Click on the “Modeling” tab and then on “New column”.
Then enter the formula: AM&PM = FORMAT(BIData[Date] , “m/dd/yy h:mm AM/PM”).
The date will then be displayed as 14/09/22 09:45 AM.
Changing the date format to months or days of the week in Power BI
The process is similar for changing the date format in Power BI to days of the week or sorting dates by month. Only the DAX formula changes.
The DAX formula for sorting dates by month: MonthName = FORMAT([Date], “mmmm”).
The DAX formula for sorting dates by day of the week: Weekdays = FORMAT(BIData[Date], “DDDD”).
Changing the date format to text in Power BI
If you wish to display dates in text format using Power BI, the method is different.
- In the pane at the top of your screen, click on “Transform” to open Power Query Editor.
- Here, click on “Add Column” and then on “Custom Column”.
- Name the column (example: “FormatText”).
- Then note the following formula: =Date.ToText(DateTime.Date([Date]), “yyyy-MM-dd”).
- Check the syntax.
- Click OK to validate the formula.
Changing the date format to a number in Power BI
Conversely, you may need the date in text format in Power BI. You will need a dedicated column.
- Click on the “Modelling” tab and then “New Column”.
- Enter the following DAX formula: dateInteger = FORMAT ( [Date], “YYYYMMDD” ).
- Then click on the “Custom Column” in your Field panel.
- Change the “Data type” to “Whole number”.
You will then have the date in number format: 20220914 (for 14 September 2022).
Conclusion
Power BI is a business intelligence (BI) tool from Microsoft that enables users to collect, model and visualise data from multiple sources and formats to improve decision-making in organisations.
It is possible to change the date format in Power BI by using the date formatting options available in the tool or by creating a new column using a DAX formula.
DAX formulas can be used to obtain different date formats, such as the abbreviation of the month, the quarter or the year only. It is also possible to change the default language of the tool to solve the problem of inverting days and months when publishing the report.
Using these techniques, you can easily change the date format in Power BI to improve the presentation of your data.
