Extract date form a column of text

[duddys]

Hi i need to extract the date from this column to use it in measure, the date is alwayst in the first place and either there is only date in the column or there is date and some text after it. I need to check in the measure, if the date is higher or lower compared to todays date and I need to check if there is some text after the date and iam completely lost on how to achieve any of those two.

[rajendraongole1]

Hi @duddys  - Use the LEFT function to extract the first 10 characters of date only.

Extracted Date =
DATEVALUE(LEFT(Table[YourColumn], 10))

Now, Create a calculated column or a measure to compare the extracted date with today’s date.

Date Comparison =
IF([Extracted Date] > TODAY(), "Future", "Past")

Use the LEN and MID functions to determine if there are characters after the 10th position.

Has Text After =
IF(LEN(Table[YourColumn]) > 10, "Yes", "No")

Hope the above approach works in your case. 

[duddys]

I did it like this but getting this error.

[Anonymous · Accepted solution]

Hi All,
Fisrtly  rajendraongole1 thank you for your solution!
And @duddys ,You are having this problem because the solution given is for use in calculation columns If you want to use it in MEASUREMENT, you can try the following solution:

ExtractedDateMeasure = 
VAR ExtractedDate =
    DATEVALUE(LEFT(SELECTEDVALUE('Table'[TextColumn]), 10))
RETURN
    ExtractedDate

IsFutureDateMeasure = 
VAR ExtractedDate =
    DATEVALUE(LEFT(SELECTEDVALUE('Table'[TextColumn]), 10))
RETURN
    IF(ExtractedDate > TODAY(), TRUE, FALSE)

HasAdditionalTextMeasure = 
VAR FullText = SELECTEDVALUE('Table'[TextColumn])
VAR RemainingText = TRIM(MID(FullText, 11, LEN(FullText) - 10))
RETURN
    IF(LEN(RemainingText) > 0, TRUE, FALSE)

If you have further questions, check out the pbix file I uploaded, I hope it helps and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @duddys - can you please try the below code.

Conditional_Formatting =
VAR DateOnly = LEFT(arcu_problems[DirectRootCauses], 10) // Extracts the date portion
VAR ParsedDate = DATEVALUE(DateOnly) // Converts text to a date
VAR CurrentDate = TODAY() // Today's date
VAR IsDateHigher = IF(ParsedDate > CurrentDate, TRUE(), FALSE()) // Check if date is higher than today
VAR TextAfterDate = TRIM(MID(arcu_problems[DirectRootCauses], 11, LEN(arcu_problems[DirectRootCauses]) - 10)) // Extracts text after the date
VAR HasTextAfter = IF(LEN(TextAfterDate) > 0, TRUE(), FALSE()) // Checks if there is text after the date

RETURN
// Example output combining the checks
"Date is " & IF(IsDateHigher, "in the future", "in the past") &
" and there is " & IF(HasTextAfter, "additional text", "no additional text") & " after the date."

still issue exist, please share the pbix file by removing the sensitive data.