Total duration from multiple entries for each distinct Work Name

[Anonymous]

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. 

[Anonymous]

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

[v-frfei-msft · Accepted solution]

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

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

[Anonymous]

@PattemManohar

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?

[PattemManohar]

@Anonymous Change the data types of StartDate and EndDate fields to Date type instead of text in the Input/Source table. Then it should resolve and my solution will work...

[Anonymous]

@Anonymous

if you are just looking for minimum and maximum for your start and end dates, you can use below DAX columns -

MIN_START = CALCULATE(MIN(Table1[Start_date]),ALLEXCEPT(Table1,Table1[Task_Name]))

MAX_END = CALCULATE(MAX(Table1[End_date]),ALLEXCEPT(Table1,Table1[Task_Name]))

[PattemManohar]

Please post the sample input data...