Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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 |
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
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)
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))
Proud to be a PBI Community Champion
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?
@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...
Proud to be a PBI Community Champion
@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]))
Please post the sample input data...
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |