Datediff by column values

[CRT]

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

[amitchandak]

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())

[CRT]

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?

[amitchandak]

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())

[edhans]

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.

[CRT]

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!

[amitchandak · Accepted solution]

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

[CRT]

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())

[edhans]

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...

[edhans]

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?