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

samdep — Thu, 13 May 2021 14:11:45 GMT

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!

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

amitchandak — Thu, 13 May 2021 15:51:02 GMT

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))

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

samdep — Thu, 13 May 2021 17:55:20 GMT

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

topic Re: DATEDIFF (Dates in Same Field) with Multi-Filter Context - Client ID & Assessment Name in DAX Commands and Tips

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

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

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