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!
Please look at your data. Customer 12345 for type 64 the first date in your table is Feb 1, 2019. The very first record in the table.
How is the formula supposed to know you don't mean that one, but you want the one that is Dec 31, 2019?