Reply
Frequent Visitor
Posts: 4
Registered: ‎08-07-2018
Accepted Solution

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. 

 


Accepted Solutions
Community Support Team
Posts: 1,608
Registered: ‎07-10-2018

Re: Total duration from multiple entries for each distinct Work Name

[ Edited ]

Hi @chippy635,

 

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)

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

View solution in original post

Attachment

All Replies
New Contributor
Posts: 583
Registered: ‎04-02-2017

Re: Total duration from multiple entries for each distinct Work Name

Please post the sample input data...

Frequent Visitor
Posts: 4
Registered: ‎08-07-2018

Re: Total duration from multiple entries for each distinct Work Name

[ Edited ]

Here is the sample input data:

 

NameWork IDTask_NameStart_dateEnd_date
John Smith198Task16/16/20177/4/2017
Jane Doe198Task16/16/20177/4/2017
Steven King198Task16/21/20176/21/2017
Mary Jane198Task16/16/20177/21/2017
John Smith703Task25/9/20176/1/2017
Michael Angelo171Task35/23/20177/10/2017
John Appleseed64Task45/16/20172/14/2018
John Smith102Task55/29/20177/10/2017
Mary Jane102Task55/29/20176/1/2017
Michael Angelo309Task66/27/20176/27/2017
John Smith309Task66/21/20177/4/2017
Jane Doe170Task76/7/201710/6/2017
Steven King170Task75/30/20176/30/2017
Highlighted
Member
Posts: 124
Registered: ‎01-21-2018

Re: Total duration from multiple entries for each distinct Work Name

@chippy635

 

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

New Contributor
Posts: 583
Registered: ‎04-02-2017

Re: Total duration from multiple entries for each distinct Work Name

[ Edited ]

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)

image.pngOutput

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

image.png

 

Frequent Visitor
Posts: 4
Registered: ‎08-07-2018

Re: Total duration from multiple entries for each distinct Work Name

@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?

Community Support Team
Posts: 1,608
Registered: ‎07-10-2018

Re: Total duration from multiple entries for each distinct Work Name

[ Edited ]

Hi @chippy635,

 

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)

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
New Contributor
Posts: 583
Registered: ‎04-02-2017

Re: Total duration from multiple entries for each distinct Work Name

@chippy635 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...

 

image.pngimage.png