Forum Discussion
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 do a column like the Diff column that finds the difference between two types of appointments (66 and 64 in the column Appt_type) for each specific customer in the column Cust.
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 | 9 |
A customer can have several appointments so I need to find the most recent, any help would be greatly appreciated!
Best regards
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
9 Replies
try like
date diff =datediff(minx(filter(table,table[Cust]=earlier(table[Cust])),table[date]),table[date],day) or date diff = if(maxx(filter(table,table[Cust]=earlier(table[Cust])),table[date])=table[date], datediff(minx(filter(table,table[Cust]=earlier(table[Cust])),table[date]),table[date],day),blank())Thank you very much!
However I can't quite get it to work, I still need to do the difference between the Appt_type value 64 vs 66?
Like this
date diff = if(maxx(filter(table,table[Cust]=earlier(table[Cust]) && [Appt_type]=66),table[date])=table[date], datediff(minx(filter(table,table[Cust]=earlier(table[Cust]) && [Appt_type]=64),table[date]),table[date],day),blank())Or
date diff = if(Sheet1[Appt_type]=66,datediff(minx(filter(Sheet1,Sheet1[Appt_type]=64 && Sheet1[Cust.]=EARLIEST(Sheet1[Cust.])),Sheet1[Date]),minx(filter(Sheet1,Sheet1[Appt_type]=66 && Sheet1[Cust.]=EARLIEST(Sheet1[Cust.])),Sheet1[Date]),DAY),BLANK())
