Forum Discussion

CRT's avatar
CRT
Frequent Visitor
6 years ago
Solved

Datediff by column values

Dear all, I'm quite new to DAX formulas and I've been searching this forum and racking my head how to do a datediff by 1) type of appointment 2) by specific customer:   I would like to be able to d...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    I added a rank column and does it

    date diff = if(Sheet1[Appt_type]=66,datediff(minx(filter(Sheet1,Sheet1[Appt_type]=64 && Sheet1[Cust.]=EARLIEST(Sheet1[Cust.]) && Sheet1[rank]=EARLIEST(Sheet1[rank])),Sheet1[Date]),minx(filter(Sheet1,Sheet1[Appt_type]=66 && Sheet1[Cust.]=EARLIEST(Sheet1[Cust.]) && Sheet1[rank]=EARLIEST(Sheet1[rank])),Sheet1[Date]),DAY),BLANK())

     

    rank = countx(filter(Sheet1,Sheet1[Appt_type]=64 && Sheet1[Date]<=EARLIER(Sheet1[Date])),[Appt_type])

     

    https://www.dropbox.com/s/yo0x4s76ww1vfuo/datediff64.pbix?dl=0

CRT's avatar
CRT
Frequent Visitor
6 years ago

Thank you very much for your help, if I change the formula from amitchandak to maxx in both instances I get the result I want!

 

date diff =
if(maxx(filter('Table';'Table'[Cust.]=EARLIER('Table'[Cust.]) && 'Table'[Appt_type]=66);'Table'[Date])='Table'[Date];
datediff(maxx(filter('Table';'Table'[Cust.]=EARLIER('Table'[Cust.]) && 'Table'[Appt_type]=64);'Table'[Date]);'Table'[Date];day);blank())
 
 
 
 
edhans's avatar
edhans
Community Champion
6 years ago

Interesting. If I change the StartDate variable to do MAX in my measure, I get the same, but neither returns the 90 days you have near the top of your data...