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, "Expi...
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"
  • 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

        It would be a column. I have found a solution using DAX in the Data Model but I would like to learn the equivalent in Power Query:

         

        Date Banding = VAR A = DATEDIFF ( TODAY (), [Actual End Date], MONTH ) RETURN IF ( ISBLANK ( [Actual End Date] ), BLANK (), SWITCH ( TRUE (), A <= 0, "Expired", A <= 2, "0-3mths", A >= 3 && A <= 5, "3-6mths", A >= 6 && A <= 11, "6-12mths", A <= 17, "12-18mths", A <= 23, "18-24mths", A >= 24, "24mths+" ) )
         
        Can this be replicated in Power Query?
         
        Thanks,
         
        Andy