cancel
Showing results for
Did you mean:
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:

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
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):

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:

5 REPLIES 5
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?

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
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):

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: