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"
V-lianl-msft
5 years agoCommunity Support
You can view the sample file I shared. There are detailed steps. First, add a custom column to calculate the date difference, then create a conditional column to group based on the date difference column.
Andy1927
5 years agoHelper I
I've tried to open your file but am unable to do so, my organisation is still using an older version of Desktop. Is there any other way I could view it?
- V-lianl-msft5 years agoCommunity Support
Hi Andy1927 ,
If you can't open the pbix file, please create a new blank query and paste the code I provided before.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.