Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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", ... )
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(...)
)
)
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" )
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" )
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(...)
)
)
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", ... )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |