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

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