Forum Discussion
Expression to search for dates
Hi everyone...
I am trying to tweak an expression, that searches a column for a year value, to account for the fact that the years 2021 and 2022 have been brought in to the source data.
The existing expression to search within "column" (which contains various text before a year) for the years 2019 or 2020 is:
Year = IF(SEARCH("*19",'Table'[column],1,0)=1,"2019","2020")
My tweak to that expression, which still only returns 2019 or 2020 is:
Year = IF(SEARCH("*19",'Table'[column],1,0)=1,"2019",IF(SEARCH("*20",'Table'[column],1,0)=1,"2020",IF(SEARCH("*21",'Table'[column],1,0)=1,"2021","2022")))
Any ideas on what I need to change? I am not sure what purpose the "=1" serves.
Many thanks and kudos to anyone who can help.
You might have a problem with your original expression as "*20" will match "2019" as well since 2019 contains the number 20.
The SEARCH function returns the starting position of the substring within the string. So you will get a match if the value starts at the position 1. But since you use a wildcard in the front, it will always get a match if the number 20 is contained within your string. So even 10/20/1999 will get matched.
Try this instead:
MATCH = IF ( SEARCH ( "2019", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2019", IF ( SEARCH ( "2020", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2020", IF ( SEARCH ( "2021", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2021", IF ( SEARCH ( "2022", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2022" ) ) ) )
4 Replies
- parry2k
Super User
Anonymous try this
Year = IF(SEARCH("*19",'Table'[column],1,0)>0,"2019",IF(SEARCH("*20",'Table'[column],1,0)>0,"2020",IF(SEARCH("*21",'Table'[column],1,0)>0,"2021","2022")))I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
- zaza
Resolver III
You might have a problem with your original expression as "*20" will match "2019" as well since 2019 contains the number 20.
The SEARCH function returns the starting position of the substring within the string. So you will get a match if the value starts at the position 1. But since you use a wildcard in the front, it will always get a match if the number 20 is contained within your string. So even 10/20/1999 will get matched.
Try this instead:
MATCH = IF ( SEARCH ( "2019", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2019", IF ( SEARCH ( "2020", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2020", IF ( SEARCH ( "2021", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2021", IF ( SEARCH ( "2022", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2022" ) ) ) )Great catch zaza . Thank you!
Thank you parry2k . It's still only working for 2019 and 2020 though. What purpose does the ">0" serve?
