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 Team,
Need inputs on executing below scenerio. as part of our tracking , we want to create a graph based on the activities.. Please find below table
ID | Activity Name | Start Date | End Date | Difference in Days |
1 | Review | 4/5/2017 | 8/5/2017 | 4 |
1 | assignment | 8/5/2017 | 10/5/2017 | 2 |
1 | approval | 10/5/2017 | 12/5/2017 | 2 |
2 | Review | 1/5/2017 | 8/5/2017 | 7 |
2 | assignment | 8/5/2017 | 11/5/2017 | 3 |
2 | approval | 11/5/2017 | 14/5/2017 | 3 |
ID=1 | Review[Start Date]-Assignment[End Date]=4/5/2017-10/5/2017=6 |
ID=2 | Review[Start Date]-Assignment[End Date]=1/5/2017-11/5/2017=10 |
So for all the project IDs, we want to track for example turn around time for a perticular project ID right from review start date to assignment end date. I am confused whether to use DAX or M language to achieve the above scenerio. It will be of great help if some one share the script to progress on this requirement.
Regards,
Sivapratap.
@Anonymous
Instead of the dates subtraction, can you just add the difference in your case?
turn around time assignment_review = CALCULATE ( SUM ( 'Table'[Difference in Days] ), FILTER ( 'Table', 'Table'[Activity Name] = "Review" || 'Table'[Activity Name] = "Assignment" ) )
Otherwise, try
turn around time assignment_review 2 = INT ( MAXX ( FILTER ( 'Table', 'Table'[Activity Name] = "Assignment" ), 'Table'[End Date] ) - MAXX ( FILTER ( 'Table', 'Table'[Activity Name] = "Review" ), 'Table'[Start Date] ) )
Thanks for the input. I will try executing and let you know
Hi All,
Below is the exact requirement, i mean i will illustrate how my data looks and what should be the outcome:
Request Number | Activity Name | Activity Date | Action Date | Time Spent in Days |
1 | Active Item | 3/29/2017 0:00 | 3/29/2017 0:00 | 0 |
1 | Design Review | 2/24/2017 0:00 | 2/28/2017 0:00 | 4 |
1 | IT Review | 2/7/2017 0:00 | 2/7/2017 0:00 | 0 |
1 | Project Initiation | 3/29/2017 0:00 | 3/29/2017 0:00 | 0 |
1 | PS | 3/1/2017 0:00 | 3/1/2017 0:00 | 0 |
1 | PS | 3/8/2017 0:00 | 3/8/2017 0:00 | 0 |
1 | PS | 3/15/2017 0:00 | 3/15/2017 0:00 | 0 |
1 | Request Initiation | 2/6/2017 0:00 | 2/6/2017 0:00 | 0 |
1 | RPA | 3/8/2017 0:00 | 3/15/2017 0:00 | 7 |
1 | RPA | 3/15/2017 0:00 | 3/29/2017 0:00 | 14 |
1 | Initial Review | 2/6/2017 0:00 | 2/7/2017 0:00 | 1 |
1 | SMP Review | 3/1/2017 0:00 | 3/6/2017 0:00 | 5 |
1 | SMP Review | 3/8/2017 0:00 | 3/8/2017 0:00 | 0 |
1 | SMP Review | 3/15/2017 0:00 | 3/15/2017 0:00 | 0 |
1 | Vendor Assignment | 2/7/2017 0:00 | 2/7/2017 0:00 | 0 |
1 | Vendor Lead Review | 2/7/2017 0:00 | 2/7/2017 0:00 | 0 |
1 | Vendor Response | 2/7/2017 0:00 | 2/24/2017 0:00 | 17 |
2 | Active Item | 3/22/2017 0:00 | 3/22/2017 0:00 | 0 |
2 | Design Review | 3/7/2017 0:00 | 3/14/2017 0:00 | 7 |
2 | Project Initiation | 3/22/2017 0:00 | 3/22/2017 0:00 | 0 |
2 | PS | 3/16/2017 0:00 | 3/16/2017 0:00 | 0 |
2 | Request Initiation | 2/27/2017 0:00 | 2/27/2017 0:00 | 0 |
2 | RPA | 3/17/2017 0:00 | 3/20/2017 0:00 | 3 |
2 | Initial Review | 2/27/2017 0:00 | 2/27/2017 0:00 | 0 |
2 | SMP Review | 3/16/2017 0:00 | 3/16/2017 0:00 | 0 |
2 | SMP Review | 3/16/2017 0:00 | 3/17/2017 0:00 | 1 |
2 | Vendor Assignment | 2/27/2017 0:00 | 2/27/2017 0:00 | 0 |
2 | Vendor Lead Review | 2/27/2017 0:00 | 3/1/2017 0:00 | 2 |
2 | Vendor Response | 3/1/2017 0:00 | 3/7/2017 0:00 | 6 |
3 | Cancellation Request by Requestor | 3/2/2017 0:00 | 3/2/2017 0:00 | 0 |
3 | Request Initiation | 2/28/2017 0:00 | 2/28/2017 0:00 | 0 |
3 | Requestor Cancellation Review by SDC Manager | 3/2/2017 0:00 | 4/7/2017 0:00 | 36 |
3 | PS | 3/5/2017 0:00 | 3/6/2017 0:00 | 1 |
3 | Initial Review | 2/28/2017 0:00 | 3/1/2017 0:00 | 1 |
3 | Vendor Assignment | 3/1/2017 0:00 | 3/1/2017 0:00 | 0 |
3 | Vendor Lead Review | 3/1/2017 0:00 | 3/1/2017 0:00 | 0 |
3 | Vendor Response | 3/1/2017 0:00 | 4/7/2017 0:00 | 37 |
4 | Request Initiation | 2/28/2017 0:00 | 2/28/2017 0:00 | 0 |
4 | Initial Review | 2/28/2017 0:00 | 3/3/2017 0:00 | 3 |
4 | Vendor Assignment | 3/3/2017 0:00 | 3/3/2017 0:00 | 0 |
4 | Vendor Lead Review | 3/3/2017 0:00 | 3/3/2017 0:00 | 0 |
4 | Vendor Response | 3/3/2017 0:00 | 4/7/2017 0:00 | 35 |
5 | IT Review | 3/1/2017 0:00 | 3/2/2017 0:00 | 1 |
5 | Request Initiation | 2/28/2017 0:00 | 2/28/2017 0:00 | 0 |
5 | Initial Review | 2/28/2017 0:00 | 3/1/2017 0:00 | 1 |
Requirement:
Difference between PS Action Date and Vendor Assignment Activity Date |
Please note,
Here we have three records PS for a perticular request number, so we need to consider the latest i.e 3/15/2017- 2/7/2017 = 36 |
the outcome table should be something like below
ID | Action Date Month | TAT(Custom Column) |
1 | March | 36 |
2 | Feb | 17 |
3 | Jan | 22 |
And finally the graph,
Please advise . I tried hit and trial with DAX queries but unable to reach the requirement. Please help. Thanks for everyones patience
@Anonymous
The TAT can be calculated by a measure, but what is the "Action Date Month" in the expected output?
Last PS Actitivy Date = MAXX(FILTER('Table','Table'[Activity Name]="PS"),'Table'[Action Date]) Vendor Assignment Date = MAXX(FILTER('Table','Table'[Activity Name]="Vendor Assignment"),'Table'[Action Date]) TAT = IF(ISBLANK([Last PS Actitivy Date]),BLANK(),INT([Last PS Actitivy Date]-[Vendor Assignment Date]))
Hi,
When i used the same query as suggested by you, i am getting the difference in 4 digits for different request numbers, but total in a single digit. Please advise on how you got the difference in days in the TAT column.
Can you please tell me how you calculated the field 'Time spent' from the 2 dates available in the fields before it. Thanks
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |