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

I'm very sorry, the last bit is a manual error, and should be saying 6 days I.e.

 

Appt_typeCust.DateDiff
641234501/02/2019 
531234503/03/2019 
491234502/04/2019 
661234502/05/201990
643246729/05/2019 
533246725/06/2019 
493246722/07/2019 
663246718/08/201981
649678420/09/2019 
539678423/10/2019 
499678425/11/2019 
669678428/12/201999
641234531/12/2019 
491234503/01/2020 
661234506/01/20206

 

The problem is that I have the same customer (12345) repeating the buying process and I am still only interested how many days it takes for him between appt_type 64 and appt_type 66, therefore instead of 339 at the end I need something that looks at the latest value for appt_type

 

Once again thank you very much!

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