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

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

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

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

    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