Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
samdep
Advocate II
Advocate II

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

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!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.