Date Function - Comparing values of type text with values of type integer

[SHOOKANSON]

Hi all,

I am trying to take a date field I have and rearrange it. Currently, it displays as either YYMMDD or YMMDD. If the year was 2008 or 2009, the 0 out front does not show in the year. To fix this I have created a new column I thought would work, but I am getting the error "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." So I added the VALUE function to convert my text to a number. Still no luck. I have tried with my column 'APLUS8FLV.CUSMS'[Cutsomer Added Date] formatted as decimal number and whole number. Here is my function:

Added Date = MID('APLUS8FLV CUSMS'[Customer Added Date], 3, 2) & RIGHT('APLUS8FLV CUSMS'[Customer Added Date], 2) & IF(LEFT('APLUS8FLV CUSMS'[Customer Added Date], 2) > 79, VALUE("0")&LEFT('APLUS8FLV CUSMS'[Customer Added Date], 1), LEFT('APLUS8FLV CUSMS'[Customer Added Date], 2))

As I am looking at this I realize that this is not going to work because the first part of my equation will not always pull the two month numbers. If anyone can help me out, I would appreciate it!

[Anonymous]

Could you post some sample data and the expected output?

[SHOOKANSON]

For example, if the customer was added on 02/22/19 or 03/20/2008, it would display as 190222 or 80320 respectively.

I would like for them to display as a date column, but the missing '0' is really messing me up.

[Anonymous · Accepted solution]

Here's the final table (did this in Power Query):

This does make the assumption that all the dates takes in the year 2000 or beyond. Didnt see a way to diffentiate the year from your sample.

Here's a rundown what is going on in Power Query ( be sure to look at the applied steps! 😞

• Added a new column that will add a zero to the start if it needs it:
• 

• Text.PadStart( 
  	Text.From( [DisplayDate]) , 
  	if
  		Text.Length( 
  			Text.From([DisplayDate])
  		)>5
  	then
  		0
  	else
  	Text.Length( 
  			Text.From([DisplayDate])
  	) +1
  
  , "0")

  basically says that if the original display date is longer than 5 characters, dont do anything. If it's less then 5 characters, add a zero in the front
• Now that are data is normalized, we can just split the new column by two characters
• That gies up the year, month and day
• Just got to add 2000 to the year column
• Then add a new column using those to build are date:

• #date( [Year] , [Month], [Day] )

  

Here's the excel file:

https://1drv.ms/f/s!Amqd8ArUSwDSzzSiLTZ4ad96BKnv

[SHOOKANSON]

Wow you make it look so easy!! Thank you very much!

[Anonymous]

Glad it helped!