Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date Difference with Filters

Project No. Tasks in the Projects Start Date End Date AB10001 XXYYZZ12 8/24/2022 9/5/2022 AB10001 XXYYZZ13 8/25/2022 9/6/2022 AB10001 XXYYZZ14 8/...
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Anonymous ,

     

    You just need one measure for all of your requirements:

    _projectLeadTime = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

     

    Here's the output when applied against different levels of dimensions:

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi Anonymous ,

 

You just need one measure for all of your requirements:

_projectLeadTime = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

 

Here's the output when applied against different levels of dimensions:

 

Pete

  • NikhilChenna's avatar
    NikhilChenna
    Skilled Sharer
    3 years ago

    Hi BA_Pete ,

    Great optimised solution brother. Averagex did not came to my mind only.

     

    Regards,

    Nikhil Chenna

     

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Perfect Brother ! Great solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    A your previous suggestion was good enough, I'd like to ask you to suggest a method to find out average of corresponding task in the projects, Since a project (for eg : xxyyzz12 - exist in every project what would be the collective average in all projects combined.

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

      Hi Anonymous ,

       

      You can adjust the scope of the measure but changing which columns are included within the SUMMARIZE function.

      You could swap 'yourTable[Project No.]'  for 'yourTable[Tasks in the Projects]' to focus the measure just on the tasks, or you could add this field into the SUMMARIZE function and keep 'yourTable[Project No.]' as well, which would probably do what you want it to.

      Try this measure and see if it does what you want:

      _projectTaskLeadTimes = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        yourTable[Tasks in the Projects],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

       

      Pete