I'm trying to calculate the number of months between rows based on a single date column. Each row contains an identifier for a particular entity and an activity date (see attached image for an example data set). I'd like to create a calculated column that will contain the number of months difference between the current row and the previous row for the same entity. Here's what I've tried so far (which isn't correct):
DateDiff("month",[ACTIVITY DATE],Max([ACTIVITY DATE]) over (Previous([ACTIVITY DATE])))
I think the issue is probably due to not expressing a way to segregate the OVER to be per entity, but I'm not sure how to structure the statment.
Can anyone help?