Average of Days difference between two dates from two related tables

[j3420]

Hi All,

I'm trying to calculate an average between the days difference from two tables. I'm using a below measure, but not getting expected results. Can anyone please help?

I retrieved dates in variables but please let me know if there's a better way to do it. Basically, i want just a date from TableA and minimum date from TableB for a Corresponding value 

Avg_Days_Diff =
VAR Dateoffered =
    CALCULATE ( FIRSTDATE ( TableB[DateOffered] ) ) //Many side of a realtionship. i want to get minimum date for a realationship value in One side of a table
VAR DateInQuestion =
    CALCULATE ( FIRSTDATE ( TableA[DateInQuestion] ) ) // One side of a relationship table. i just want a get a date value
VAR DaysDiff =
    DATEDIFF ( DateInQuestion, Dateoffered, DAY )
RETURN
    AVERAGEX ( VALUES ( TableA[IDNum] ), CALCULATE ( DaysDiff ) )

Expected Result:

[TomMartens]

Hey @j3420 

unfortunately I do not understand, what the expected result should look like.

The table iterator uses the table A but the value of the variable "DateInQuestion" is determined outside of the iteration, this means this value will be the same for each IDNum of TableA.
This is also valid for the variable "Dateoffered".

What is the relationship between the tables A and B?

Avg_Days_Diff =
AVERAGEX ( 
	VALUES ( TableA[IDNum] )
	, 
	VAR Dateoffered =
		CALCULATE ( FIRSTDATE ( TableB[DateOffered] ) )
	VAR DateInQuestion =
		CALCULATE ( FIRSTDATE ( TableA[DateInQuestion] ) )
	VAR DaysDiff =
		DATEDIFF ( DateInQuestion, Dateoffered, DAY )
	RETURN
	DaysDiff
)

Maybe this is what you are looking for or at least provides you with some ideas.

Regards,

Tom

[j3420]

Hey Tom,

I have just uploaded the picture of expected result. Expected result i have obtained using calculated column in TableA.

Relationship is one to Many. TableA (one side) -* TableB(Many side) on IDNum column.

Hope this helps

[TomMartens · Accepted solution]

Hey @j3420 ,

try this:

Avg_Days_Diff =
AVERAGEX ( 
	VALUES ( TableA[IDNum] )
	,
	var	_IDNum = [IDNum]
	VAR Dateoffered =
		CALCULATE ( FIRSTDATE ( TableB[DateOffered] ) , 'TableB'[IDNum] = _IDNum )
	VAR DateInQuestion =
		CALCULATE ( FIRSTDATE ( TableA[DateInQuestion] ) )
	VAR DaysDiff =
		DATEDIFF ( DateInQuestion, Dateoffered, DAY )
	RETURN
	DaysDiff
)

Regards,

Tom

[j3420]

It worked!

Thank you very much!