Forum Discussion

aqeel_shaikh's avatar
aqeel_shaikh
Icon for Helper III rankHelper III
1 year ago
Solved

Aging(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 ...
Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
aqeel_shaikh
Icon for Helper III rankHelper III
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    That is to be written as a calculated column.

    Age = 
DATEDIFF (
    'Table'[Project Actual Start Date],
    COALESCE ( 'Table'[Expected Completion Date], TODAY () ),
    DAY
)