Forum Discussion
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...
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
I'm very sorry, the last bit is a manual error, and should be saying 6 days I.e.
| Appt_type | Cust. | Date | Diff |
| 64 | 12345 | 01/02/2019 | |
| 53 | 12345 | 03/03/2019 | |
| 49 | 12345 | 02/04/2019 | |
| 66 | 12345 | 02/05/2019 | 90 |
| 64 | 32467 | 29/05/2019 | |
| 53 | 32467 | 25/06/2019 | |
| 49 | 32467 | 22/07/2019 | |
| 66 | 32467 | 18/08/2019 | 81 |
| 64 | 96784 | 20/09/2019 | |
| 53 | 96784 | 23/10/2019 | |
| 49 | 96784 | 25/11/2019 | |
| 66 | 96784 | 28/12/2019 | 99 |
| 64 | 12345 | 31/12/2019 | |
| 49 | 12345 | 03/01/2020 | |
| 66 | 12345 | 06/01/2020 | 6 |
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!
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