Here’s a question from our reader, Martina:
I have a table that contains date values. For reporting purposes, i would like to transform those dates into a custom text string which includes the date, month and year, which i would like to use when creating tables and charts in my report. Any ideas are appreciated here.
Create a text column from a date in Power BI
Define date values
You most probably have a date column, but if not, we will start by creating a simple table and populate it with random dates.
- In the Power BI Data view, hit the New Table button.
- In your Formula bar type the following DAX statement that will generate a table containing all dates during the first two weeks of February 2024.
Dates = CALENDAR (DATE (2024,2,1), DATE(2024,2,14))- Next, rename the Dates column, by clicking its head, hit Rename and change the name to Full Date.
- Note that a table named Dates was created and is available in the Data pane at the right hand side of your Data View tab.
Transform dates to text string with DAX
We will use the FORMAT function to create a new column consisting of the format DD MMMM YYYY.
- In the Data View, hit New Column.
- Then type the following DAX statement into your formula tab and hit Enter.
Date Text = FORMAT('Dates'[Full Date],"D of MMMM YYYY")- This will create the Date Text column that contains the custom date format that you specified in your DAX formula:
Calculate the Month-Year value from a date column
Next case we will touch upon is to calculate the MM-YYYY combination our of standard timestamp.
- Again, in your Power BI Data view, hit New Column.
- Then type of following DAX formula:
Month Year = FORMAT('Dates'[Full Date],"MM-YYYY")- A column that combines the Month – Year value will be created.
- Last step, save your Power BI report.
Convert from date to text format using PowerQuery
A more recommended approach is to execute any required data conversions right when getting the data from the source. For this purpose we use the Power Query capability, available when hitting the Transform Data button in Power BI.
Proceed as following to convert your DateTime values to a Date in Power Query:
- First off, load your data from your data source (Excel, CSV, an SQL database etc.).
- The Power Query interface will open up.
- Now in the Power Query Editor, select the column that contains the DateTime values.
- Right-click on the selected column header and select Change Type , then select Text.
- Select whether to Create a new Transformation step (recommended) or overwrite the existing step.
- To persist your changes in the data model, hit Close and Apply.
FAQ
How to change a year column display to an alternative format such as YY or mmYYYY?
From the Data, Table and Model view you can modify your year format right from the Columns Tools tab. For example:
Short Year = FORMAT (Dates_tutorial[Date],"YY")How adding a year column affect performance of my model?
From previous experience, adding custom calculated columns affects query loading and performance of large datasets. In case that your dataset is large, make sure to create additional date columns right in your SQL database, Excel spreadsheet or other relevant sources.