cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SHOOKANSON Frequent Visitor
Frequent Visitor

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

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!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nick_M Senior Member
Senior Member

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

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

Final Table.png

 

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:
  • Display with zero if neede.png
  • 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] )
    table with YMD and Date built.png

Here's the excel file:

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

5 REPLIES 5
Nick_M Senior Member
Senior Member

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

Could you post some sample data and the expected output?

 

 

SHOOKANSON Frequent Visitor
Frequent Visitor

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

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.

Highlighted
Nick_M Senior Member
Senior Member

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

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

Final Table.png

 

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:
  • Display with zero if neede.png
  • 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] )
    table with YMD and Date built.png

Here's the excel file:

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

SHOOKANSON Frequent Visitor
Frequent Visitor

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

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

Nick_M Senior Member
Senior Member

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

Smiley HappyGlad it helped!