Aging(Days) column should be stopped

[aqeel_shaikh]

"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

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

[shafiz_p]

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

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!! 

[aqeel_shaikh]

@shafiz_p , @danextian Thanks it worked

[aqeel_shaikh]

Thanks Shahariar, just one more thing i have multiple table in powerbi, now by default the table is selected as per alphbetical order. and the table in powerbi is Procurement Project, can you please rewrite the above script so i can pull the table.

regards,

Aqeel