Forum Discussion
Total duration from multiple entries for each distinct Work Name
Hi everyone,
I have some billing data where multiple employees will submit their Hours, Start_date, and End_date for each time they work on a task. There are many tasks, and each can have multiple people contributing.
I need to calculate the duration (difference between start_date and end_date) for each task. However, since there are many entries, the sum by function ends up overcalculating.
I'm looking for a way to create a new table where:
for each distinct Task_Name,
find the earliest date entered from Start_date as Date.Min
find the latest date entered from End_date as Date.Max
calculate the difference (in days)
and that result will be displayed under a new column variable called Duration, with the corresponding Task_Name.
Any suggestions for using M or DAX? Thank you.
Hi Anonymous,
We can create a measure to meet your requirement.
Measure = var mindate = CALCULATE(MIN(input[Start_date]),ALLEXCEPT(input,input[Task_Name])) var maxdate = CALCULATE(MAX(input[End_date]),ALLEXCEPT(input,input[Task_Name])) return DATEDIFF(mindate,maxdate,DAY)
For more details, please check the pbix as attached.
Regards,
Frank
7 Replies
- PattemManohar
Community Champion
Please post the sample input data...
Here is the sample input data:
Name Work ID Task_Name Start_date End_date John Smith 198 Task1 6/16/2017 7/4/2017 Jane Doe 198 Task1 6/16/2017 7/4/2017 Steven King 198 Task1 6/21/2017 6/21/2017 Mary Jane 198 Task1 6/16/2017 7/21/2017 John Smith 703 Task2 5/9/2017 6/1/2017 Michael Angelo 171 Task3 5/23/2017 7/10/2017 John Appleseed 64 Task4 5/16/2017 2/14/2018 John Smith 102 Task5 5/29/2017 7/10/2017 Mary Jane 102 Task5 5/29/2017 6/1/2017 Michael Angelo 309 Task6 6/27/2017 6/27/2017 John Smith 309 Task6 6/21/2017 7/4/2017 Jane Doe 170 Task7 6/7/2017 10/6/2017 Steven King 170 Task7 5/30/2017 6/30/2017 - PattemManohar
Here is the expected output...
Add a "New Table" as below.
TasksOutput = SUMMARIZE(Tasks,Tasks[Task_Name],"StartDate",MIN(Tasks[Start_date])
,"EndDate",MAX(Tasks[End_date])
)Add a "New Column" as below.
DaysDiff = DATEDIFF(TasksOutput[StartDate],TasksOutput[EndDate],DAY)
Output
If you want only TaskName and DaysDiff then use this by adding "New Table"
TasksOutput1 = SUMMARIZECOLUMNS(Tasks[Task_Name],"DaysDiff",DATEDIFF(MIN(Tasks[Start_date]),MAX(Tasks[End_date]),DAY))
Thanks, however for task1 the max end date should be July 21st, not July 4th. How can the code be adjusted to calculate the correct maximum end dates?
v-frfei-msftCommunity Support
Hi Anonymous,
We can create a measure to meet your requirement.
Measure = var mindate = CALCULATE(MIN(input[Start_date]),ALLEXCEPT(input,input[Task_Name])) var maxdate = CALCULATE(MAX(input[End_date]),ALLEXCEPT(input,input[Task_Name])) return DATEDIFF(mindate,maxdate,DAY)
For more details, please check the pbix as attached.
Regards,
Frank
