Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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))
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)
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))
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)
User | Count |
---|---|
85 | |
76 | |
72 | |
69 | |
56 |
User | Count |
---|---|
104 | |
99 | |
92 | |
78 | |
69 |