cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

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

Jarda

3 ACCEPTED SOLUTIONS
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver III

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

Solution Sage

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"
)```
3 REPLIES 3
Solution Sage

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"
)```
Resolver III

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors