In this Microsoft Power BI Tutorial, We will discuss how to convert this yyyymmdd format to Date in the Power BI Report.
By taking a simple example, I will show you how you can get the date from the yyyymmdd in the Power BI Report.
Convert yyyymmdd to date in Power BI Report
In this example, I have a SharePoint Online List details name as Budgets. This SharePoint Online Budget list has below columns as:
- Customer Name (By default it is a Title column)
- BUD_AMOUNT (Currency Data type)
- Budget Month (Number Data type)
- Budget Year (Single line of Text Data type)
- BUD_DTM (Single line of Text Data type)
You can see the Budgets list in the below screenshot. Here in this list, the BUD_DTM (Single line of text) column is appearing in the below format:
20190105 (YYYYMMDD) where 2019 represents Year, 01 represents Month and 05 represents Day as shown in the below screenshot.
When you will create a report in the Power BI Desktop and add this BUD_DTM column in the X-Axis, then you can see only the number instead of the date format as shown below screenshot.
This is not a perfect date format that you can use in the Power BI Report. You have to make it format as YYYYMMDD. Follow the below process to convert it as a Date format.
In the Power BI Desktop page, Go to the Modeling tab and click on the New Column under the Calculations section as like the below screenshot.
Once you will click on New Column, then a formula bar will appear where you have to apply the below formula to get the exact date format.
DateFormatted = Date(Left([BUD_DTM],4),Right(left([BUD_DTM],6),2),right([BUD_DTM],2))
Here, BUD_DTM is the list column where the value represents as 20190105.
Also, you can follow the below screenshot to add the formula in the formula bar.
When you applied the above formula in the formula bar, then a new column (Dateformatted) will be added to the Budgets table. Now you have to change the Power BI Column Data type from Text to Date/Time.
To change the column data type, Select the new column (Dateformatted) and go to the Modeling tab. Then select the Data type as Date/Time from the drop-down as you can see in the below screenshot.
Once you will change the data type as Date/Time, then you can see the date format as shown in the below screenshot.
And also the new column (DateFormatted) will appear in the Date Hierarchy format as like below screenshot.
When you will add this new column (DataFormatted) to the Axis section of any visualization, then you can see only the Year but not the month and date.
To view the proper date format, click on the two-pin icon that is present on the top of the visual. Once you will click on this icon, then you can see all the year, month and date with a proper date format as like below screenshot.
Also, You may like the following Power BI tutorials:
- Get Month Name from Month Number in Power BI
- How to use Power BI sync slicers
- SharePoint List Calculated Column Division #DIV/0!
- 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
- 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 convert yyyymmdd to date
- Power BI convert hours to minutes
In this Microsoft Power BI Tutorial, We discussed how to convert this yyyymmdd format to Date in the Power BI Report.
By taking a simple example, I have explained to you to get the date from the yyyymmdd 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”