topic convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date in DAX Commands and Tips

convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date

PStokes — Tue, 19 Nov 2019 07:56:42 GMT

Re: convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date

Anonymous — Tue, 19 Nov 2019 11:07:31 GMT

PStokes Please try below formula. in case it doesn't work for you please share two three record for each formate

DDMMYYYY = DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],2))

In case of DMMYYYY please check the length of column and if it is 8 then you can use above else

dMMYYYY =
VAR _length = len('table'[column])
RETURN IF(_length=7,DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],1)),DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],2)))

Re: convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date

PStokes — Fri, 29 Nov 2019 17:45:00 GMT

Thanks, I tried to add colum and use the VAR as written but received a sytax error

Examples of the data below.

Date Text

1012019

21012019

27112019

8112019

Re: convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date

PStokes — Fri, 29 Nov 2019 18:19:43 GMT

sorry saw my syntax error and corrected it.

This worked if I chnaged the MID expression to MID('table'[date],2,2) for 7 character dates and MID('table'[date],3,2) for 8 character dates

successful formulae below

thank you very much

Date =

VAR _length = LEN('table'[date])

RETURN IF(_length=7,DATE(RIGHT('table'[date],4),MID('table'[date],2,2),LEFT('table'[date],1)),DATE(Right('table'[date],4),MID('table'[date],3,2),LEFT('table'[date],2)))