Date and time formatting

[RossC]

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

[themistoklis · Accepted solution]

@RossC

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

[RossC]

Thanks that worked perfectly!!!