Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |