Forum Discussion
Helper IIIAging(Days) column should be stopped
"Aging(Days)" column should be stopped when the "Project completion date" or "Expected completion date" is reached.
currently my aging is calculated as [Project Actual Start date - Today()] which is calculated field in powerbi. hence the aging doesn't stop.
data set is attached herewith.
| Project Actual Start Date | Expected Completion Date | Project Completion Date | Aging(days) |
| 06/02/2025 00:00 | 03/03/2025 00:00 | 01/03/2025 00:00 | 26 |
| 01/02/2025 00:00 | 28/02/2025 00:00 | 25/02/2025 00:00 | 31 |
| 26/02/2025 00:00 | 05/03/2025 00:00 | 6 | |
| 28/02/2025 00:00 | 07/03/2025 00:00 | 05/03/2025 00:00 | 4 |
| 15/01/2025 00:00 | 05/02/2025 00:00 | 48 | |
| 09/10/2024 00:00 | 11/02/2025 00:00 | 146 | |
| 03/02/2025 00:00 | 19/02/2025 00:00 | 29 | |
| 19/02/2025 00:00 | 26/02/2025 00:00 | 13 | |
| 10/02/2025 00:00 | 10/04/2025 00:00 | 22 | |
| 06/02/2025 00:00 | 12/02/2025 00:00 | 17/02/2025 00:00 | 26 |
| 13/11/2024 00:00 | 21/02/2025 00:00 | 20/02/2025 00:00 | 111 |
ok. You need to use aggregator function, like min, max etc. Try update the formula with min aggr. as follows:
Aging(Days) = VAR CompletionDate = IF( NOT(ISBLANK(MIN('Procurement Project'[Project Completion Date]))), MIN('Procurement Project'[Project Completion Date]), MIN('Procurement Project'[Expected Completion Date]) ) RETURN IF( NOT(ISBLANK(CompletionDate)), DATEDIFF(MIN('Procurement Project'[Project Actual Start Date]), CompletionDate, DAY), DATEDIFF(MIN('Procurement Project'[Project Actual Start Date]), TODAY(), DAY) )Hope this helps!!
8 Replies
- shafiz_p
Super User
Hi aqeel_shaikh Try this:
Aging(Days) = VAR CompletionDate = IF( NOT(ISBLANK('Table'[Project Completion Date])), 'Table'[Project Completion Date], 'Table'[Expected Completion Date] ) RETURN IF( NOT(ISBLANK(CompletionDate)), DATEDIFF('Table'[Project Actual Start Date], CompletionDate, DAY), DATEDIFF('Table'[Project Actual Start Date], TODAY(), DAY) )Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz- aqeel_shaikh
shafiz_p - Hi, can you please check what is wrong here iam getting error
Ageing(Days) = VAR CompletionDate = IF( NOT(ISBLANK('Procurement Project'[ProjectCompletionDate])), 'Procurement Project'[ProjectCompletionDate], 'Procurement Project'[Expected Completion Date] ) RETURN IF( NOT(ISBLANK(CompletionDate)), DATEDIFF('Procurement Project'[Project Actual Start Date], CompletionDate, DAY), DATEDIFF('Procurement Project'[Project Actual Start Date], TODAY(), DAY) )- shafiz_p
ok. You need to use aggregator function, like min, max etc. Try update the formula with min aggr. as follows:
Aging(Days) = VAR CompletionDate = IF( NOT(ISBLANK(MIN('Procurement Project'[Project Completion Date]))), MIN('Procurement Project'[Project Completion Date]), MIN('Procurement Project'[Expected Completion Date]) ) RETURN IF( NOT(ISBLANK(CompletionDate)), DATEDIFF(MIN('Procurement Project'[Project Actual Start Date]), CompletionDate, DAY), DATEDIFF(MIN('Procurement Project'[Project Actual Start Date]), TODAY(), DAY) )Hope this helps!!
- danextian
Hi aqeel_shaikh
Try this:
DATEDIFF ( 'table'[project start date], COALESCE ( 'table'[project completion date], TODAY () ), DAY )COALESCE will return the first non-blank value so if there's a value for completion date, that will be the end date else TODAY().
- aqeel_shaikh
getting below error...pls. help!
Ageing(Days) = DATEDIFF(FILTER('Procurement Project','Procurement Project'[Project Actual Start Date].[Date], COALESCE(FILTER('Procurement Project','Procurement Project'[Expected Completion Date].[Date], TODAY() ), DAY )))- danextian
That is to be written as a calculated column.
Age = DATEDIFF ( 'Table'[Project Actual Start Date], COALESCE ( 'Table'[Expected Completion Date], TODAY () ), DAY )
