Calculate TAT from two different but related Tables

[Anonymous]

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

projectID	ProjectName	Approval Initiator	Created
1	Project1	Initiator1	27/12/2021 04:56:23
2	Project2	Initiator2	27/12/2021 08:00:33
3	Project3	Initiator3	28/12/2021 04:32:56
4	Project4	Initiator4	29/12/2021 11:11:28

 Approvals Tables

projectID	ProjectName	Approver	Approval TIme	Outcome
1	Project1	Approver1	01/01/2022 03:37:00	Returned
1	Project1	Approver1	01/01/2022 04:22:45	Returned
1	Project1	Approver1	01/01/2022 06:47:22	Delegation
1	Project1	Approver1	01/01/2022 :22:45	Returned
2	Project2	Approver1	02/01/2022 :00:36	Returned
2	Project2	Approver1	03/01/2022 :05:30	Delegation
3	Project3	Approver2	04/01/2022 :01:34	Approver

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 

[Anonymous · Accepted solution]

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

[ryan_mayu]

@Anonymous 

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 .

[Anonymous · Accepted solution]

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

[ryan_mayu · Accepted solution]

@Anonymous 

is this what you want?

Column = (Approvals[Approval TIme]-RELATED(Projects[Created]))/24

pls see the attachment below

[Anonymous]

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

[ryan_mayu]

@Anonymous 

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)