Forum Discussion
Solved
Custom column - IF statements within date interval
Hello everyone! I have an ecommerce order table with data that looks like below: Date Email Homeloan Sales value New column (converted) 2020-01-01 [email protected] Yes 0 Yes 2020-0...
Is there a reason this needs to be done in the query editor? If not, you can do it as a calculated column (or a measure). Here is a calculated column expression that returns your desired result.
Converted = VAR thisdate = HomeSales[Date ] VAR saledate = CALCULATE ( MIN ( HomeSales[Date ] ), ALLEXCEPT ( HomeSales, HomeSales[Email] ), HomeSales[Date ] > thisdate, HomeSales[Sales value] > 0 ) VAR daysbetween = DATEDIFF ( thisdate, saledate, DAY ) RETURN IF ( HomeSales[Homeloan] = "Yes", IF ( daysbetween <= 30, "Yes", "No" ) )Regards,
Pat
Hi andersbq
Paste this M code in a blank query to see the steps. It could be just done in one step but I've split it in more so that it is easier to follow. You could merge all the steps in one if needed.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR2lrPyMPIeU/FS95PxcIDcytRhIGijF6iDUGRlgqvPLBxKGpqgKIQYm5qXiNdAUaiCqOoSBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Email = _t, Homeloan = _t, #"Sales value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales value", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(inner)=> inner[Email] = [Email])[Date]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Duration.Days(List.Max([Custom])-List.Min([Custom]))>30 then "Yes" else "No"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Converted", each if List.Min([Custom]) = [Date] then [Custom.1] else ""),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"})
in
#"Removed Columns"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
