Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
shansenTrek
Frequent Visitor

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:

Data Table.png

 

Here is the Table Visual:

project table.png

 

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

Table Visual.png

 

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:

Table Visual.png

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

 

Thanks!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
vanessafvg
Super User
Super User

can you dump out the data in text?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Hi, @shansenTrek 

 

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

Table:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.