Hi, I'm having some issues to do what I've mentioned on the above and would like to know if anyone can bring some light on what I'm doing wrong.
I have two tables:
- [Table_Details]: Bank movements for all users
- [Table_User] : List of users
I want to put in [Table_User] two columns with the latest date of the movement for the user and the value for this date. For example, if having this:
[Table_Details]
| User | Movement | Value |
| A1 | 01/Nov/2022 | $500 |
| A1 | 01/Oct/2022 | $600 |
| A2 | 08/Nov/2022 | $250 |
| A2 | 07/Nov/2022 | $700 |
| A3 | 02/Oct/2022 | $400 |
| A3 | 09/Oct/2022 | $125 |
I should get this for columns [Last Movement] and [Last Value]
| User | Name | Last Movement | Last Value |
| A1 | XXXX | 01/Nov/2022 | $500 |
| A2 | YYYY | 08/Nov/2022 | $250 |
| A3 | ZZZZ | 09/Oct/2022 | $125 |
I've been able to create the relationship in Dataverse [1:Many] but not sure how to progress. I've tried:
- Formula in column: First/Last are not Functions I can use
- Calculated column: Not able to find how to find the max date
- Roll up column: I've able to create the Roll up with Max Date but It is not populating anything. Not sure how to get the value for [Last Value] based on that
- Lookup column: I think I can use this to find the value based on Max Date and the user type but I wasn't able to configure it.
Any help is much appreciated.
