calculate number of days between 2 dates in same column

[d_brew]

I have a complex model that was brought in from Dynamics AX. In the data model, there is a 'Work Orders Table' that contains the field 'ACTUALEND' ( the end date for each work order).  There is also an 'Objects' table containing the field 'Object Name'. 

I'm pretty new to DAX and given the size of the workbook, I'd assume that creating a measure for these types of calculations would be preferreable to save space (this report also needs to be able to meet the size requirements for scheduled refresh using PowerBI online). 

There is a 1 to Many relationship between Work Orders and Work Order Line using the Work Order RECID; and a Many to 1 relationship from Work Order Line to Object using the Object RECID.

Using this relationship, I'd like to add a measure to the values of this matrix showing the days between the most recent date for each row. In other words, the days between each work order for indivdual objects. 

Ultimately, I'll use that measure to create another measure that computers the average days between work orders for all of the objects, using a visual that can be drilled down to show averages for individual objects. 

I've tried getting this off the ground for a while and I'm pretty twisted around at this point. Any help in the right direction would be greatly apprciated. 

[v-shex-msft]

Hi @d_brew,

You can try to use below measures if it suitable for your requirement:

Diff=
var currDate=Max(WorkOrderLine[ActualEnd])
var currOrder=LASTNOBLANK(WorkOrderLine[WorkOrder],[WorkOrder])
var prevDate=MAXX(FILTER(ALL(WorkOrderLine),[WorkOrder]=currOrder&&[ActualEnd]<currDate),[ActualEnd])
return
IF(prevDate=blank(),0,DateDiff(prevDate,currDate,Day))

AVG Per WorkOrder=
var currOrder=LASTNOBLANK(WorkOrderLine[WorkOrder],[WorkOrder])
return
AVERAGEX(FILTER(ALL(WorkOrderLine),[WorkOrder]=currOrder),[Diff]

 If above is not help, can you provide some sample data to test?

Regards,

XIaoxin Sheng