Need help with Measure to calculate date difference between 2 dates (from 2 tables)

[p-ha]

Hello All, I'm new with PowerBI and currently struggling with the subject task. Any help is greatly appreciated.

Background info: The tables are pre-designed by IT team, data are pulled through Analytic Services.

Table 1 (included data at Header level) - Requisition Table

Table 2 (included data at Line Level) - Requisition Line Table

Relationship between table 1 & 2 - None

Table 1 has relationship with a Table 3 - tied by Requistion Key ID

Table 2 has relationship with a Table 3 - tied by Requisition Key Line ID

Table 1
REQ #	REQ Approved Date
REQ 001	1/1/2020
REQ 002	5/2/2020
REQ 003	10/23/2020

Table 2
REQ #	REQ Line #	REQ Approved Date (exist only on Table 1)	Need By date (Exist only on table 2)	Date Diff between REQ approved date to Need by date
REQ 001	1	1/1/2020	1/30/2020	*=? (should be 30)
REQ 001	2	1/1/2020	1/30/2020	*=? (should be 30)
REQ 002	1	5/2/2020	4/30/2020	*=?
REQ 003	1	10/23/2020	12/24/2020	*=?
REQ 003	2	10/23/2020	10/25/2020	*=?
REQ 003	3	10/23/2020	12/24/2020	*=?

.

I can create a measure using SumX & Datediff if both the date (REQ approved date and Need by Date) is on the same table 1. But in this case, the Need by Date is on Table 2, and the REQ approved date is data on Table 1, I'm not sure how to create a measure to calculate the days between the Approved date vs. the need by date, and average them to have just 1 result per each REQ.

For instance, REQ 001 should result with 30 days (both lines have same approved and need by date), while REQ 003 may not yield similar result, as REQ 003 line 2 has a different need by date.

Thank you in advance for your time and your help!

[p-ha]

@wdx223_DanielI think I'm almost there. The Measure doesn't pop any error, but I think I need to specify further the relationship between tables as it may alter the provided measure.

Table 1 has:

REQ #

REQ approved date

REQ Key

REQ ID (other data point, but consistent with REQ #)

Table 2 has:

REQ Line #

REQ Need by date

REQ Line Key

REQ ID

Table 3 has:

REQ Key

REQ Line Key

Table 1 has 1-to-* relationship with Table 3 (REQ Key)

Table 2 has 1-to-* relationship with Table 3 (REQ Line Key)

P/s: Also, when I edit the measure to use the REQ ID instead of REQ #, and when I drag the Measure over, it results in "date" in stead of the # of days. How can we fix this? (I need to show for eg.: REQ approved 1/1/2020, REQ need by 1/5/2020, A-day = 5 days)

Thanks!!

[wdx223_Daniel]

=VAR _DateFromCurrentTable=max(table2[Need By Date) VAR _reqs=values(table2[Req#]) RETURN _DateFromCurrentTable-calculate(max(table1[REQ Approved Date]),TREATAS(_reqs,table1[REQ #]))+1

[p-ha]

Hi Daniel, thanks for guidance, can you please help elaborate further whether this is 1 measure or multiple measures? I'm still trying to grasp the logic from the suggested measure. Thanks again!

[wdx223_Daniel]

@p-ha it is one mearsue. if it does not work, please show us the relationship between these three tables and which table these fields in the visual come from.