Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Datagulf
Responsive Resident
Responsive Resident

Calculate TAT from two different but related Tables

I have two tables that I need to find Turn around Time of the Approvers to approve projects the tables are as follows.

Projects Table

projectIDProjectNameApproval InitiatorCreated
1Project1Initiator127/12/2021 04:56:23
2Project2 Initiator227/12/2021 08:00:33
3Project3 Initiator328/12/2021 04:32:56
4Project4 Initiator429/12/2021 11:11:28


 Approvals Tables

projectIDProjectNameApproverApproval TImeOutcome
1Project1Approver101/01/2022 03:37:00Returned
1Project1Approver101/01/2022 04:22:45Returned
1Project1Approver101/01/2022 06:47:22Delegation
1Project1Approver101/01/2022 :22:45Returned
2Project2Approver102/01/2022 :00:36Returned
2Project2Approver103/01/2022 :05:30Delegation
3Project3Approver204/01/2022 :01:34Approver

Each project is unique and is entered once in the projects table. However, a project is repeated severally in the approvals table depending with the number of times it was interacted with by the different approvers

I would like to create a TAT calculation for each Approver.
The formula for the first approver is 


(Approval time - Created Time)/24.


(This information is in different tables)
The Formula for the subsequent approvers is


(Approval Time User - Approval Time User Before)/24


Assist with a measure that can achieve that...

@amitchandak 

2 ACCEPTED SOLUTIONS

@ryan_mayu , the data is from an a Power app application and a single project can have mutiple approvers, the approvers table displays that.. 

 

View solution in original post

@Datagulf 

is this what you want?

Column = (Approvals[Approval TIme]-RELATED(Projects[Created]))/24
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@Datagulf 

what's the expected output based on the sample data? 

how to get the approval time? e.g. we have 4 approval time of project 1 .





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu , the data is from an a Power app application and a single project can have mutiple approvers, the approvers table displays that.. 

 

@Datagulf 

is this what you want?

Column = (Approvals[Approval TIme]-RELATED(Projects[Created]))/24
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hey, this only solves the first part of the equation for the first approver, for subsequent approvers, one needs to subtract the CUrrent Approver time to the Immediate former Approver time, and divide by 24.. 

An if statement for the above would  work 

Formulae =IF First Approver
 (Approvals[Approval TIme]-RELATED(Projects[Created]))/24
ELse 
(Aprovals[Approval TIme] Below line for the same project - Aprovals[Approval TIME]Same Line for the same project)/24


 

@Datagulf 

pls try this

Column = 
var _FIRST=CALCULATE(min('Approvals'[Approval TIme]),ALLEXCEPT(Approvals,Approvals[projectID]))
VAR _next=minx(FILTER(Approvals,Approvals[projectID]=EARLIER(Approvals[projectID])&&Approvals[Approval TIme]>EARLIER(Approvals[Approval TIme])),Approvals[Approval TIme])
return if(Approvals[Approval TIme]=_FIRST, (Approvals[Approval TIme]-RELATED(Projects[Created]))/24, (_next-Approvals[Approval TIme])/24)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors