Forum Discussion
Datediff by column values
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
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())
This will get you started:
Appointment Date Diff = --VAR CurrentAppointmentType = MAX('Table'[Appt_type]) VAR CurrentCustomer = MAX('Table'[Customer]) VAR AppointmentCustomerGrouping64 = FILTER( ALL('Table'), 'Table'[Appt_type] = 64 && 'Table'[Customer] = CurrentCustomer ) VAR AppointmentCustomerGrouping66 = FILTER( ALL('Table'), 'Table'[Appt_type] = 66 && 'Table'[Customer] = CurrentCustomer ) VAR StartDate = CALCULATE( MIN('Table'[Date]), AppointmentCustomerGrouping64 ) VAR EndDate = CALCULATE( MAX('Table'[Date]), AppointmentCustomerGrouping66 ) VAR DateDifference = DATEDIFF(StartDate,EndDate,DAY) VAR Type66 = IF(MAX('Table'[Appt_type]) = 66, DateDifference, 0) RETURN Type66The problem is this shows the first date for a customer of appt type 64 and compares to the last date for that same customer in appt 66. Your table seems to show you have some other way to group data. For example, you are showing that for customer 12345 at the bottom that type 64 is Dec 31, 2019, and type 66 is Jan 6, 2020. Which is 7 days, not 9. Not sure where 9 comes from.
But the first date for customer 1234 of type 64 isn't Dec 31, 2019, it is Feb 1, 2019, thus the result my measure returns of 339 days.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?
