Forum Discussion
Datediff by column values
- 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
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
Type66
The 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!
- edhans6 years agoCommunity Champion
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?
- amitchandak6 years agoSuper User
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
- CRT6 years agoFrequent Visitor
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())- edhans6 years agoCommunity Champion
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...