Forum Discussion

Andy1927's avatar
Andy1927
Helper I
5 years ago
Solved

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, "Expired", IF([Count of Month] <3, "0-3mths"))
 
How to I change the query to show that if the Count of Month is between 0 and 3 then "0-3mths".
 
I will need to continue this for 3-6, 6-12 mths and so on
 
Thanks
 
Andy
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    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"

10 Replies

    • Andy1927's avatar
      Andy1927
      Helper I
      5 years ago

      Sorry but this still does not provide me a solution.

       

      Is there a simple way that I can band (group) dates from Todays date showing anything before today as "Expired"  and then 0-3 mths, 3-6mths and so on?

       

      Thanks

       

      Andy

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        5 years ago

        [Count of Month] Is it a column or a measure?Do you want to create measure or column?

  • Andy1927's avatar
    Andy1927
    Helper I
    5 years ago

    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")