In this Microsoft Power BI Tutorial, We will discuss how to convert the Month name from Month number, how to sort by the Month name field in the Power BI Report.
By taking a simple example, I will show you how you can get all the month names from the month number in the Power BI Report.
Get month name from month number in Power bi
In this example, I have a Budget data report details. This report has a Month column which is Whole Number Data type.
This Month column contains the numerical values like 1 for January, 2 for February, 3 for March and so on.
In the below screenshot, I have taken a Slicer filter that contains Month. In the second visualization, you can see the Budget amount depending upon each month which is presented in X-axis.
To convert the Month Name from the Month Number in the Power BI Report, You have to do the follow things as:
- Right-click on your Data table (Here, My Data table is Budgets).
- Click on the New column.
Once you will click on the New Column option, then a formula bar will appear on the top of the page. In that formula bar, you need to put the below formula as:
MonthName = FORMAT(DATE(1, [Month], 1), "MMM")
Also, you can follow the below screenshot to add the formula in the formula bar.
If you will write “MMM” in the formula bar, then you can only get the first three-digit of month name i.e. Jan, Feb, Mar, etc as shown below.
When you will write “MMMM” in the formula bar, then you will get all the digit of month name i.e. January, February, March, etc.
As I have written as “MMM” in the formula bar, thats why in the Slicer filter the month name is appearing like Jan, Feb, Mar and so on.
Note:After creating the new column (Month Name), take the Month Name field instead of Month in the Slicer filter. Then only you can see the difference.
Similarly, In the Stacked column chart, take the Month Name (new column) instead of Month in the X-axis. Then you can see the month name as Jan, Feb, Mar, etc. as like the below screenshot.
Not only you can see the difference in the List slicer filter, But also you can do the same thing in the Dropdown option. Just click on the Dropdown.
Once you will click on the Dropdown option, then it will appear as below. When you will expand the All, then you can see all the Month name as below screenshot.
Read Power BI Switch – DAX function
Sort By Month Name Column in Power BI Report
When you will add the Month name column to the Slicer filter, then by default it will appear randomly, which means not in ascending or descending order.
To sort the Month Name Column in the Power BI Report, First of all, select the Month Name column from the Budgets table as shown below.
Go to the Modelling tab and Click on Sort by Column. Then select the Month column from the drop-down.
Once you will select the Month column, then you can see the month name will appear in ascending order as shown below.
Similarly, in the Power BI Visual Bar also, the Month Name will appear in ascending order in the X-axis.
Now when you will select any of the month names from the slicer, then the data value will appear on that month basis.
Here in the below screenshot, As I have selected the month as “Apr“, So the Budget Details are appearing of that April month basis as like below screenshot.
Also, You may like the following Power BI tutorials:
- Power bi shared dataset permissions management
- Scheduled Power BI report data refresh
- Power BI nan error (Not a number) while dividing by Zero
- Power BI report using People Picker Field
- Power BI Measure Sum and Subtract Example
- Power BI Calculated Column Example
- Power bi conditional column example
- How to split column in Power bi
- How to change data source in Power Bi
- Power BI Admin Portal
- Power BI Quick Insights
- Power BI Group By Examples
- How to use Power BI sync slicers
- Power BI convert hours to minutes
- Power BI convert yyyymmdd to date
Hence in this Microsoft Power BI Tutorial, We discussed how to get the Month name from Month number, how to sort by the Month name in the Power BI Report.
By taking a simple example, I have explained to you to get all the month names from the month number in the Power BI Report.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”