No of Days taken to raise PO Calculation

[jayanthan]

Hi All 

When i have below given PR and PO tables , how do i calculate no of days taken to raise PO from PR

Table 1
Date	PR ID	Value
5/1/2019	A	100
5/1/2019	B	80
5/1/2019	C	60
5/1/2019	D	50
5/1/2019	E	100

Table 2
PR ID	PO ID	Date	Value
A	X	5/11/2019	100
B	X	5/11/2019	80
C	Y	5/16/2019	60
D	Z	5/17/2019	50
E	W	5/13/2019	60
E	S	5/6/2019	40

Regards

Jayanthan

[Mariusz · Accepted solution]

Hi @jayanthan 

You can add a column to Table2 and use the expresion below.

Column = DATEDIFF(RELATED(Table1[Date]), Table2[Date], DAY)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jayanthan]

---

@Mariusz wrote:

Hi @jayanthan 

You can add a column to Table2 and use the expresion below.

Column = DATEDIFF(RELATED(Table1[Date]), Table2[Date], DAY)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

Hi  Mariusz

Wow.this is fine thanks, but how can i do this when i have many to many relationship tables . In the below given new example , lets  say PR ID " E" has value in two rows, then how do i it 

Table 1
Date	PR ID	Value
5/1/2019	A	100
5/1/2019	B	80
5/1/2019	C	60
5/1/2019	D	50
5/1/2019	E	60
5/1/2019	E	40

Regards

Jayanthan

[Mariusz]

Hi @jayanthan 

Can you be a bit more specific in what you are looking to achieve?

Thanks
Mariusz

[jayanthan]

Hi @Mariusz 

How do i calculate no of days taken to raise PO from below example , where PR ID is duplicated in both tables

Table 1
Date	PR ID	Value
5/1/2019	E	60
5/1/2019	E	40

Table 2
Date	PR ID	PO ID	Value
5/10/2019	E	X	60
5/12/2019	E	X	40

[Mariusz]

Hi @jayanthan 

You always have to have one side distinct, otherwise you multiplying the outcom.

do you have any other ID's in your table that can create a link between the values?

Many Thanks

Mariusz

[jayanthan]

Hi Mariusz

Yes, i have the line id  which can used to create one to many relationships between tables. it works fine now

Thank You so much for the support 

Regards

Jayanthan