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

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper User
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!!