Average Between Dates

[mparker123]

Hello Everyone,

I have two date fields a Project Start Date and a Project End Date. I have multiple projects I want to find the average duration of the projects by Project type and Business Unit. I attached a sample file to this discussion. The Straight table labeled "average by project type" and "average by business unit" In both tables there is a duration, the duration is taking the earliest start date and the latest due date of each project type or business unit and giving me the duration between them. I am trying get the average of each project type and business unit.

[sunny_talwar]

May be your want this

Avg(Aggr([Due Date] - [Start Date], [Project Type], Summary))

Avg(Aggr([Due Date] - [Start Date] , [Business Unit], Summary))

[mparker123]

That works however i need to add an if statement or a set analysis to limited the expression.

If(Avg(Aggr(epic_due_date - epic_cf_start_date, epic_cf_pmo_business_unit, epic_summary) and DEPT='44840' and project_name <> 'PMO IMS Pipeline'))

This is the expression I am trying to use however it doesn't seem to be working

[sunny_talwar]

May be try this

Avg(Aggr(Only({<Project_name -= {'PMO IMS Pipeline'}, DEPT = {'44840'}>}[Due Date] - [Start Date], [Project Type]), Summary))

[mparker123]

We are missing a parenthesis somewhere I tried to but it in at the end of the expression and at the end of the set analysis it doesn't seem to be working.

[sunny_talwar]

My bad, may be this

Avg(Aggr(Only({<Project_name -= {'PMO IMS Pipeline'}, DEPT = {'44840'}>}[Due Date] - [Start Date]), [Project Type], Summary))