DATEDIFF (Dates in Same Field) with Multi-Filter Context - Client ID & Assessment Name

[samdep]

Hi Community!

I have a field that contains dates by assessment for every client -- my goal is to calculate the datediff between each assessment (in the order they are given) by client. I'm not interested in the client level detail, rather I'm ultimately looking to show the overall average number of days between assessments (from Assessment 1 to Assessment 2, Assessment 2 to Assessment 3... and so on...) across all clients.

Since the dates are all in the same field, as are the assessment names, I figured I'd just build a few calculated columns.

I created a calc column for Assessment 1 and pulled in the date, now I am looking to pull in the date for Assessment 2 by client since it's a calc column and each record is client specific - then, I'll figure out the datediff and average. 

There's probably a much simpler way of doing this, but I'm getting the following error message with my DAX: Exp refers to multiple columns... mult columns cannot be convered to single value. 

Assessment 2 Date = 

VAR Client = 'Assessment'[ClientID]

VAR Assessment_2 = FILTER('Assessment', 'Assessment'[Assessment_Name__c] IN {"AssessmentTwo"})

RETURN

CALCULATE(MIN('Assessment'[CreatedDate]),

FILTER('Assessment',

'Assessment'[ClientID] = Client &&

'Assessment'[Name] = Assessment_2
))

Example - My Table (The Blue Text is where I'm looking to go...)

ClientID  AssessmentName  Date               Assessment 1 Date (Calc)      Assessment 2 Date (Calc)    A1-A2 DateDiff   

1234       Assessment1          1/1/2021       1/1/2021                               1/5/2021                                4

1234       Assessment2          1/5/2021

There's probably a simpler, better way to achieve - and the above approach will cause some duplication in the data, but the average won't be impacted. And then, I used MIN on the off chance that a client has two assessments with the same name - as a result of an error in data entry.

Thanks!

[amitchandak · Accepted solution]

@samdep , Create a measure like

averageX(summarize(Table, Table[client], "_1", calculate(min(Table[Date]), filter(table, Table[AssessmentName] = "Assessment1" )), "_2", calculate(max(Table[Date]), filter(table, Table[AssessmentName] = "Assessment2" ))), datediff([_1],[_2],day))

[samdep]

@amitchandak thank you so much! this worked perfectly - so much better than multiple calc columns. thank you!