Forum Discussion
Andy1927
5 years agoHelper I
Date Banding
Hi I'm trying to write a Nested IF statement within the Data Model but I'm getting stuck as I need to use BETWEEN parameters to group my data e.g Banding = IF([Count of Month] <0, "Expi...
- 5 years ago
Please refer to:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BDcAwCAPAXfKupZoobZglyv5rlPRjP0/YwFot7uggord9/QoMPMJECpUTKuidKbzeSZ8kaGLoEms5hUAXhsfsIZ599cT+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual End Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Date(DateTime.FixedLocalNow() )), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each ((Date.Year([Actual End Date])-Date.Year([Custom]))*12) + Date.Month([Actual End Date]) - Date.Month([Custom])), #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= 0 then "Expired" else if [Custom.1] <= 2 then "0-3mths" else if [Custom.1] <= 5 then "3-6mths" else if [Custom.1] <= 11 then "6-12mths" else if [Custom.1] <= 17 then "12-18mths" else if [Custom.1] <= 23 then "18-24mths" else "24mths+"), #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Actual End Date", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom", "Custom.1"}) in #"Removed Columns"
Andy1927
5 years agoHelper I
Thanks, I have tried the following but the results only show 0-3mths
Banding = SWITCH(TRUE(),[Count of Month] <0, "Expired", [Count of Month] <=3, "0-3mths", [Count of Month] <=6, "3-6mths", [Count of Month] <=12, "6-12mths", "0thers")