Forum Discussion

Jarda's avatar
Jarda
Regular Visitor
7 years ago
Solved

how to split number into interval

Hello all, I would like to create new column an "aging interval". It should be a diff between the column "Posting date" and actual date and divided into the 6 month intervals (0-3M, 4-6M, 7-12M, 13-...
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Sample data would help but I would suggest creating a column using a SWITCH statement and use DATEDIFF something like:

     

    Column = SWITCH(DATEDIFF([Date1],[Date2],MONTH),
    0,"0-3M",
    1,"0-3M",
    2,"0-3M",
    3,"0-3M",
    4,"4-6M",
    5,"4-6M",
    6,"4-6M",
...
)
  • Arentir's avatar
    Arentir
    7 years ago

    Hi Jarda,

     

    You could have two calculated columns

    DiffMonth = DATEDIFF([Posting Date],[Actual Date], MONTH)

    Aging Interval = IF ( Table1[DiffMonth] <= 3,"0-3M",
                                      IF (Table1[DiffMonth] <= 6, "4-6M",
                                           IF(...)
                                         )
                                   )

     

  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    A calcualted column like this should help:

    Aging Interval =
VAR MonthsBetween =
    DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], MONTH )
RETURN
    SWITCH (
        TRUE (),
        MonthsBetween <= 3, "0-3M",
        MonthsBetween <= 6, "4-6M",
        MonthsBetween <= 12, "7-12M",
        MonthsBetween <= 24, "13-24M",
        MonthsBetween <= 36, "25-36M",
        ">36M"
    )
AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

A calcualted column like this should help:

Aging Interval =
VAR MonthsBetween =
    DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], MONTH )
RETURN
    SWITCH (
        TRUE (),
        MonthsBetween <= 3, "0-3M",
        MonthsBetween <= 6, "4-6M",
        MonthsBetween <= 12, "7-12M",
        MonthsBetween <= 24, "13-24M",
        MonthsBetween <= 36, "25-36M",
        ">36M"
    )