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

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