Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |