Forum Discussion
Date and time formatting
Hi I'm new here and trying to figure out DAX as I go, I have an issue with a Power BI Dashbard that I am trying to create from a downloaded report from an Old system, and I want to be able to refresh the data and let Power BI format the data for me.
The report shows the date format as 20180424140009, I need to convert this number to
Year 2018,
Month 04 (April),
Day 24th
Hour 14:00
Min 00
Seconds 09
I have managed to do this in excel, but I don't really want to have to modify an excel file each time I want to refresh the report. The Excel formula that works is below and only creates the date. The time isn't required.
This formula only divides up the characters and then Power BI recognises them as a Date.
=TEXT(DATE(LEFT(L2,4),MID(L2,5,2),MID(L2,7,2))+TIME(MID(L2,9,2),MID(L2,11,2),MID(L2,13,2)),"dd/mm/yyyy")
Ideal output would be to conver this 20180424140009, to this 24/04/2018 as a Date Hierarchy
Thanks in advance for any help
Ross
The DAX formula in PowerBi will be pretty much the same as the one in Excel.
Creata a new column and not measure and add the following code:
datenew = MID('Table'[DateColumn], 7,2) & "/" & MID('Table'[DateColumn], 5,2) & "/" & LEFT('Table'[DateColumn],4)Then go to Modelling tab on top, select the datenew column and change the data type to Date
2 Replies
- themistoklis
Community Champion
The DAX formula in PowerBi will be pretty much the same as the one in Excel.
Creata a new column and not measure and add the following code:
datenew = MID('Table'[DateColumn], 7,2) & "/" & MID('Table'[DateColumn], 5,2) & "/" & LEFT('Table'[DateColumn],4)Then go to Modelling tab on top, select the datenew column and change the data type to Date
Thanks that worked perfectly!!! :smileyvery-happy:
