Forum Discussion
Solved
Next Visit Type by Visit Date
I have a table of client visits and visit dates (all future). There are multiple visit types that could be scheduled, but I would like to report what the next visit type is, not the when.
Here is a small sample:
| Patient Name | Visit Type | Visit Date |
| John Smith | Regular | 7/25/2024 |
| Betty Jones | Annual | 8/1/2024 |
| Bob Johnson | Regular | 7/10/2024 |
| John Smith | Annual | 8/25/2024 |
| Betty Jones | Regular | 9/1/2024 |
| Bob Johnson | Annual | 8/15/2024 |
For this example, I would like the results to look like this:
| Patient Name | Next Scheduled Visit Type |
| John Smith | Regular |
| Betty Jones | Annual |
| Bob Johnson | Regular |
I know how to calculate when the next visit is, but I don't know how to return the value in Visit Type based on that calculation.
Any and all help appreciated. Thank you
Hi aflintdepm
Would a measure like this help?
Next Visit Type = VAR _NextDate = CALCULATE( MIN( 'Table'[Visit Date] ), 'Table'[Visit Date] > TODAY() ) VAR _NextType = CALCULATE( MAX( 'Table'[Visit Type] ), 'Table'[Visit Date] = _NextDate ) RETURN _NextType
1 Reply
Hi aflintdepm
Would a measure like this help?
Next Visit Type = VAR _NextDate = CALCULATE( MIN( 'Table'[Visit Date] ), 'Table'[Visit Date] > TODAY() ) VAR _NextType = CALCULATE( MAX( 'Table'[Visit Type] ), 'Table'[Visit Date] = _NextDate ) RETURN _NextType