Dax help - duration between days

[sv12]

Hi,

I am trying to calculate the duration in days from the previous row for each contact name. (Column in red)

Sample Data:

Column to be calculated: Duration from previous activity (Days)

Contact Name	Activity Type	Activity Date	Duration from previous activity (Days)
Tony Stark	Call	1/1/2020	0
Tony Stark	Call	1/3/2020	2
Tony Stark	Email	1/7/2020	4
Tony Stark	Visit	1/10/2020	3
Tony Stark	Call	1/10/2020	0
Spider Man	Email	1/1/2020	0
Spider Man	Call	1/3/2020	2
Spider Man	Call	1/7/2020	4
Spider Man	Call	1/9/2020	2

I understand that recursive calculations in Power BI can be a pain!

I was hoping if I could get some help in figuring out this calculated column.

Thanks!

[tex628 · Accepted solution]

Sort the table ascending and add an index. then add the following calculated column:

Duration = 
VAR activityD = 'Table'[Activity Date]
VAR index = 'Table'[Index]
VAR previous = CALCULATE(MAX('Table'[Activity Date]);ALL('Table');'Table'[Index]<index ; 'Table'[Contact Name] = EARLIER('Table'[Contact Name]))
return
IF( activityD - previous = 43831 ; BLANK() ; activityD - previous)

Should give you a result like this:


Hope it helps,

/ J

[sv12]

@tex628  Thanks for your response. 

Is it possible to modify your DAX to have the calculations work for "Tony stark" and "Spiderman" seperatly? Just like partitioning in SQL. 

In that case the 1st value for each contact would be 0 and then the calculation runs. 

Any insights on this?

Thanks

[tex628]

This calculations takes the contact names into account, look at index 7/8! Or am i missunderstanding you?