Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!
Solved! Go to 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])
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])
@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]))
can you dump out the data in text?
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.
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 |
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |