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.