Forum Discussion
DATEDIFF Difference (in a Row context iterator)
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.
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)
2 Replies
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)
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.
