Period Column

Hi,
This post was added to our 'Improve the forum suggestions' thread.
I have moved it to our Power BI thread.
Apologies for the delay.
Regards, Rich

You can add a Period column in Power BI in "MMM-yy" format using two main approaches:

**1. Using DAX (Data Analysis Expressions) in Power BI Desktop (Recommended for flexibility):**

This method creates a new calculated column in your data model. It's generally preferred because it keeps the original date column as a date data type, allowing for proper sorting and date-based calculations, while the new column is a text representation for display.

* **Steps:**

1. Go to the **Data View** or **Report View** in Power BI Desktop.

2. In the "Fields" pane, right-click on your table and select **"New column"**.

3. In the formula bar that appears, enter the following DAX formula, replacing `YourDateColumn` with the actual name of your date column:

```dax
Period = FORMAT('YourTable'[YourDateColumn], "MMM-yy")
```

* `Period` is the name of your new column.
* `YourTable` is the name of the table containing your date column.
* `YourDateColumn` is the name of the column you want to format.
* `"MMM-yy"` is the format string:
* `MMM` gives the abbreviated month name (e.g., Jan, Feb).
* `yy` gives the two-digit year (e.g., 23, 24).

4. Press **Enter** to create the column.

* **Sorting Consideration:**
Since the "Period" column created with DAX is a text field, it will sort alphabetically (e.g., Apr-23, Aug-23, Dec-23, Feb-23...). To sort it chronologically, you'll need to sort it by your original date column:

1. Select the `Period` column in the "Fields" pane.
2. Go to the **Column tools** tab in the ribbon.
3. Click on **"Sort by column"** and select your original **date column** (e.g., `YourDateColumn`).

**2. Using Power Query Editor (M Language) for data transformation:**

This method modifies your data during the loading process. It can be useful if you prefer to have the formatted column available directly in your transformed data.

* **Steps:**

1. Go to the **Home** tab in Power BI Desktop and click on **"Transform data"** to open the Power Query Editor.

2. In the Power Query Editor, select the table you're working with.

3. Select your date column.

4. Go to the **Add Column** tab and click on **"Custom Column"**.

5. In the "Custom Column" dialog box:

* Enter a **New column name** (e.g., "Period").

* Enter the following M formula, replacing `[YourDateColumn]` with the actual name of your date column:

```powerquery
Date.ToText([YourDateColumn], "MMM-yy")
```

* `Date.ToText` is the function to convert a date to text.
* `[YourDateColumn]` refers to your date column.
* `"MMM-yy"` is the format string.

6. Click **"OK"**.

7. You'll now have a new column with the "MMM-yy" format. This column will be of "Text" data type.

8. Close and Apply your changes to load the data back into Power BI Desktop.

* **Sorting Consideration:**
Similar to the DAX method, this column will also be text and will sort alphabetically by default. You'll still need to sort it by your original date column in Power BI Desktop (as described in the DAX section) for chronological sorting in visuals.

**Choosing the right method:**

* **DAX is generally preferred** when you need to maintain the date data type for calculations and advanced sorting, and only need a formatted version for display.
* **Power Query** is useful if you want the formatted string as part of your data model during the initial data loading and transformation, especially if you're not planning complex date calculations on that specific formatted column.

Both methods will achieve the "MMM-yy" format for your Period column. Remember to choose the one that best fits your data modeling and reporting needs.