Forum Discussion

shansenTrek's avatar
shansenTrek
Frequent Visitor
6 years ago
Solved

Average Days to Complete a Task

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!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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])

5 Replies

    • shansenTrek's avatar
      shansenTrek
      Frequent Visitor
      6 years ago

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

       

      DateHoursEmployee NameRegionProject NameTask Names
      08/19/20198SusanWestProject ATask 3
      08/20/20198SusanWestProject ATask 3
      08/21/20198SusanWestProject ATask 3
      08/16/20198SusanWestProject ATask 3
      08/14/20198SusanWestProject ATask 3
      11/05/20199JoeEastProject CTask 3
      11/08/201910JoeEastProject CTask 3
      11/07/20198JoeEastProject CTask 3
      06/03/20194MikeWestProject BTask 1
      05/28/20197MikeWestProject BTask 1
      05/30/20194MikeWestProject BTask 1
      05/29/20198MikeWestProject BTask 1
      05/03/20191MikeWestProject BTask 1
      06/04/20198SusanWestProject ATask 1
      06/03/20198SusanWestProject ATask 1
      10/08/20192JoeEastProject CTask 1
      02/19/20203MikeWestProject BTask 5
      03/25/202012SusanWestProject BTask 4
      03/24/20208SusanWestProject BTask 4
      01/22/20205MikeWestProject BTask 4
      11/18/20196JoeEastProject CTask 4
      11/22/20198JoeEastProject CTask 4
      11/21/20199JoeEastProject CTask 4
      10/18/20198SusanWestProject ATask 4
      10/17/20198SusanWestProject ATask 4
      10/16/20198SusanWestProject ATask 4
      10/15/20198SusanWestProject ATask 4
      01/29/20202JoeEastProject CTask 4
      10/25/20198JoeEastProject CTask 2
      10/23/20198JoeEastProject CTask 2
      10/22/20198JoeEastProject CTask 2
      10/21/20198JoeEastProject CTask 2
      07/24/20198SusanWestProject ATask 2
      07/23/20198SusanWestProject ATask 2
      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        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.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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]))

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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])