DATEDIFF Difference (in a Row context iterator)

[Anonymous]

Hello,

I have a table like this:

NAME 			COURSE				COMPONENT	     DATE_COMPLETED
==========		=========			===========	     ===============
John Doe 1		Course	1			Component 1			Date 1
John Doe 1		Course	1			Component 2     		Date 2
John Doe 1		Course	1			Component 3     		Date 3
John Doe 1		Course	1			Component 4     		Date 4
John Doe 2  		Course  2			Component 1     		Date 5
John Doe 2  		Course  2			Component 2     		Date 6
John Doe 2  		Course  2			Component 3     		Date 7
John Doe 2  		Course  2			Component 4     		Date 8
John Doe 3  		Course  1			Component 1     		Date 9
John Doe 3  		Course  1			Component 2     		Date 10
John Doe 3  		Course  1			Component 3     		Date 11
John Doe 3  		Course  1			Component 4     		Date 12

 and my desired output needs to 

Name				COMPLETION_DAYS
=====				===========================
John Doe 1			DATEDIFF(Date 4,Date 1,DAYS)
John Doe 2			DATEDIFF(Date 5,Date 8,DAYS)
John Doe 2			DATEDIFF(Date 9,Date 12,DAYS)

Basically, what I need to do is to use an iterator in a row context that checks a specific critera (e.g name, course). Once matched it would calculate the difference between the component 1 and 4 dates.

I think the other apprach would be to extract to tables (One for component 1 and the other for component 4) and just subtract the difference there, but any other suggestions are welcome.

[d_gosbell]

You could do this in a calculated column with something like the following:

Total Course Days = 
var _currentName = Table1[Name]
var _currentCourse = Table1[Course] 
var _comp1Date = LOOKUPVALUE(Table1[Date_Completed], Table1[Name], _currentName, Table1[Course], _currentCourse,Table1[Component], "Component 1")
var _comp4Date = LOOKUPVALUE(Table1[Date_Completed], Table1[Name], _currentName, Table1[Course], _currentCourse,Table1[Component], "Component 4")
var _days = DATEDIFF(_comp1Date,_comp4Date, DAY)
return _days

This will repeat the total course days for every component within the one course and name. Or if you wanted you could wrap the DATEDIFF in an IF to only return a value for the first or last component.

You could probably also write this logic in a measure, but then you would have to aggregate the days across multiple courses somehow with something like a Sum or Average and you would not get the 2 "John Doe 2" lines.

[parry2k · Accepted solution]

@Anonymous try this measure, drop table visual, put name and MyDate (following measure) in values and you are good to go.

MyDate = 
VAR startDate = 
CALCULATE( MIN( Table[Date_Completed] ),
ALLEXCEPT( Table, Table[Name] )
)
VAR endDate = 
CALCULATE( MAX( Table[Date_Completed] ),
ALLEXCEPT( Table, Table[Name] )
)
RETURN
DATEDIFF(endDate, startDate, DAY)