how to split number into interval

[Jarda]

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-24M, 25-36M, >36M). Could anyone advise?

Thank you ina advance.

Jarda

[Greg_Deckler · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[Arentir · Accepted solution]

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(...)
                                     )
                               )

[Greg_Deckler · Accepted solution]

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