Average Days to Complete a Task

[shansenTrek]

Hi Team,

First time poster, long time reader/follower.

I am trying to create a measure to give me the average days to complete a task.  We have multiple people working on several projects that start at different times throughout the year.  Each project requires four to five tasks to complete.  I can get the number of days to complete a task by project but when I try to get the average across all projects, the measure uses the first date the task was recorded and the last date of the task, instead of calculating the sum of days from each projects task.  Here is some sample data:

Here is the Table Visual:

Now, the table visual with the average days.  Average days for task 1 should be 12 days.

Here are the simple measures I am currently using:

Start Date = FIRSTDATE( 'Table'[Date] )

Last Date = LASTDATE( 'Table'[Date] )

# of Days = DATEDIFF( [Start Date] , [Last Date] , DAY )

Project Count = DISTINCTCOUNT( 'Table'[Project Name] )

Avg Days = DIVIDE( [# of Days] , [Project Count] , 0 )

I would also like to visually filter average days by region. Here is an example.  Average days for Task 4 in the West Region should be 231 days. Projects A and B are in the West Region:

To summarize, the "Avg Days" measure is incorrect and I need help to resolve it.

Thanks!

[Greg_Deckler · Accepted solution]

Perhaps:

Avg # of Days = 

  VAR __Table = 

    SUMMARIZE(

      'Table',

      [Project],

      "Start Date",FIRSTDATE( 'Table'[Date] ),

      "Last Date",LASTDATE( 'Table'[Date] ),

      "# of Days",DATEDIFF( [Start Date] , [Last Date] , DAY )
    )

RETURN

  AVERAGEX(__Table,[# of Days])

[amitchandak]

@shansenTrek ,Try like

AverageX(summarize('Table', 'Table'[Project Name], 'Table'[Task Name],"_diff",datediff( FIRSTDATE( 'Table'[Date] ),LASTDATE( 'Table'[Date] ),day))
,sum([_diff])/distinctCOUNT([Project Name]))

[vanessafvg]

can you dump out the data in text?

[shansenTrek]

Will this work? I'm new to this so let me know if there is an easier way to do it.

Date	Hours	Employee Name	Region	Project Name	Task Names
08/19/2019	8	Susan	West	Project A	Task 3
08/20/2019	8	Susan	West	Project A	Task 3
08/21/2019	8	Susan	West	Project A	Task 3
08/16/2019	8	Susan	West	Project A	Task 3
08/14/2019	8	Susan	West	Project A	Task 3
11/05/2019	9	Joe	East	Project C	Task 3
11/08/2019	10	Joe	East	Project C	Task 3
11/07/2019	8	Joe	East	Project C	Task 3
06/03/2019	4	Mike	West	Project B	Task 1
05/28/2019	7	Mike	West	Project B	Task 1
05/30/2019	4	Mike	West	Project B	Task 1
05/29/2019	8	Mike	West	Project B	Task 1
05/03/2019	1	Mike	West	Project B	Task 1
06/04/2019	8	Susan	West	Project A	Task 1
06/03/2019	8	Susan	West	Project A	Task 1
10/08/2019	2	Joe	East	Project C	Task 1
02/19/2020	3	Mike	West	Project B	Task 5
03/25/2020	12	Susan	West	Project B	Task 4
03/24/2020	8	Susan	West	Project B	Task 4
01/22/2020	5	Mike	West	Project B	Task 4
11/18/2019	6	Joe	East	Project C	Task 4
11/22/2019	8	Joe	East	Project C	Task 4
11/21/2019	9	Joe	East	Project C	Task 4
10/18/2019	8	Susan	West	Project A	Task 4
10/17/2019	8	Susan	West	Project A	Task 4
10/16/2019	8	Susan	West	Project A	Task 4
10/15/2019	8	Susan	West	Project A	Task 4
01/29/2020	2	Joe	East	Project C	Task 4
10/25/2019	8	Joe	East	Project C	Task 2
10/23/2019	8	Joe	East	Project C	Task 2
10/22/2019	8	Joe	East	Project C	Task 2
10/21/2019	8	Joe	East	Project C	Task 2
07/24/2019	8	Susan	West	Project A	Task 2
07/23/2019	8	Susan	West	Project A	Task 2

[v-alq-msft]

Hi, @shansenTrek 

Based on your description, I created data to reproduce your scenario.

Table:

You may create two measures as below.

SumDays = 
var t1 = 
SUMMARIZE(
    'Table',
    'Table'[Project Name],
    'Table'[Task Names],
    "Start Date",FIRSTDATE('Table'[Date]),
    "Last Date",LASTDATE('Table'[Date])
)
var t2 = 
ADDCOLUMNS(
    t1,
    "# of Days",DATEDIFF([Start Date],[Last Date],DAY)
)
var _task = SELECTEDVALUE('Table'[Task Names])
return
SUMX(
        FILTER(
            t2,
            [Task Names] = _task
        ),
        [# of Days]
)

AvgDays = 
var t1 = 
SUMMARIZE(
    'Table',
    'Table'[Project Name],
    'Table'[Task Names],
    "Start Date",FIRSTDATE('Table'[Date]),
    "Last Date",LASTDATE('Table'[Date])
)
var t2 = 
ADDCOLUMNS(
    t1,
    "# of Days",DATEDIFF([Start Date],[Last Date],DAY)
)
var _task = SELECTEDVALUE('Table'[Task Names])
return
DIVIDE(
    SUMX(
        FILTER(
            t2,
            [Task Names] = _task
        ),
        [# of Days]
    ),
    COUNTROWS(
        FILTER(
            t2,
            [Task Names] = _task
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.