Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sv12
Helper III
Helper III

Dax help - duration between days

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 NameActivity TypeActivity DateDuration from previous activity (Days)
Tony StarkCall1/1/20200
Tony StarkCall1/3/20202
Tony StarkEmail1/7/20204
Tony StarkVisit1/10/20203
Tony StarkCall1/10/20200
Spider ManEmail1/1/20200
Spider ManCall1/3/20202
Spider ManCall1/7/20204
Spider ManCall1/9/20202

 

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!

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

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:
image.png

Hope it helps,

/ J


Connect on LinkedIn

View solution in original post

3 REPLIES 3
tex628
Community Champion
Community Champion

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:
image.png

Hope it helps,

/ J


Connect on LinkedIn

@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
Community Champion
Community Champion

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


Connect on LinkedIn

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.