How to convert and calculate text to date while ignoring incorrect values?

[Anonymous]

Hello all,

i have text in column in format YYYY-MM. I'd like to convert text to date, but I'd like to add 2 months to the selection and also ignore incorrect values. See table below. Any idea how to do it?

curent format	should result in
2016-01	March 2016
2016-02	April 2016
2016-03	May 2016
2016-10	December 2016
2016-11	January 2017
2016-13	ignore
2016-99	ignore
2016-00	ignore

I tried to do some workarounds in DAX and Calculated Columns with splitting columns and such, but didn't get results I'd like to see. 😞

Many thanks a in advance.

Best regards

MV

[Anonymous · Accepted solution]

This would be far better to do before DAX (in Power Query / "M"), but... since i played with it... 

Plus2Month = var RawDate = IFERROR(DATEVALUE(Table1[RawDate] & "-01"), datevalue("1-1-1900")) return if (YEAR(RawDate) = 1900, BLANK(), Date(Year(RawDate), Month(RawDate)+2, 1))

[Anonymous · Accepted solution]

This would be far better to do before DAX (in Power Query / "M"), but... since i played with it... 

Plus2Month = var RawDate = IFERROR(DATEVALUE(Table1[RawDate] & "-01"), datevalue("1-1-1900")) return if (YEAR(RawDate) = 1900, BLANK(), Date(Year(RawDate), Month(RawDate)+2, 1))

[Anonymous · Accepted solution]

Via M:

#"Inserted Parsed Date" = Table.AddColumn(#"Renamed Columns", "ParseDate", each Date.From(DateTimeZone.From([RawDate])), type date),
#"Plus 2 Months" = Table.AddColumn(#"Inserted Parsed Date", "Plus 2 Months", each Date.AddMonths([ParseDate], 2), type date)