none
Calculate the Higher of Two Dates in a Project Level Custom Field. RRS feed

  • Question

  • I recently posted a question about accurately displaying project progress in Power BI as % complete is not always a reliable indicator.

    To address my requirement I want to create a project level custom date field that calculates the Maximum of either the Actual Finish Dates or Stop dates (for in progress tasks) in the schedule.

    This should result in a calculated date that represents where the project has been progressed to in time.

    I have explored the options for creating this calculated value but cannot see a Max function. I am not an Excel guru so don't know what all the functions can deliver.

    Can anyone suggest the correct calculation to employ?


    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Monday, February 24, 2020 8:59 AM

Answers

  • Hi Dom,

    Calculate the latest date per task, and roll the max up to the summary level.

    The formula you need can be based off % complete - IIf([% Complete]=100,[Actual Finish],[Stop]) 



    Ben Howard [MVP] | web | blog | book | downloads | P2O

    • Marked as answer by Dominic Moss Monday, February 24, 2020 10:23 AM
    Monday, February 24, 2020 9:52 AM

All replies

  • Hi Dom,

    Calculate the latest date per task, and roll the max up to the summary level.

    The formula you need can be based off % complete - IIf([% Complete]=100,[Actual Finish],[Stop]) 



    Ben Howard [MVP] | web | blog | book | downloads | P2O

    • Marked as answer by Dominic Moss Monday, February 24, 2020 10:23 AM
    Monday, February 24, 2020 9:52 AM
  • Hi Ben,

    Thanks for the suggestion and illustrated example - how does this sit with the known impact of having a large number of task fields with calculations? 

    I was hoping to use a Project level field and calculation - I have had a go and the [Actual Finish] and [Stop] fields can allegedly be referenced at the project level (they are available as fields to select when creating a formula), I just need to find a way to get it to deliver the same result as Roll up Maximum.

    Alternatively as the intent of this requirement is to accurately reflect things in Power BI would there be a way of doing the calculation in Power BI itself with no need to create a calculated field in Project Online at all?


    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Monday, February 24, 2020 10:23 AM
  • Hi Dom,

    This soln obviously has an impact on the number of formulas..., but it's not a complex formula in truth.

    I'm sure that this could be done in Power Query, but I don't see the point unless the performance of the formula was causing major issues.


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Monday, February 24, 2020 10:43 AM
  • Hi Ben,

    I have created the custom field with the Formula as you suggested - I have had a play around with Stop and Resume dates as the referenced field in the formula. Interesting that the Stop and Resume dates have tended to coincide with the end of a working day when displaying date and time format whereas the Summary Progress bar uses the start of the next working day - small beer in the grand scheme of things but a quirk of the system.

    I will see how we get on using this, as you say the formula is not overly complex and as such probably doesn't place much demand on processing.


    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Tuesday, February 25, 2020 12:10 PM