Forum Discussion
Calculate the time between dates on different rows
Hi,
I'm struggling to create a calculated column that calculates DAYSDIFF between the 10th March and 17th March (please note: the start date is to the right of the actualend date)
This is just an extract of the table, there are some records when there is only 1 row per OwnerID, and and not multiple rows per OwnerID. when happens then the values in Time in Deferral AP is sufficient.
Is something like this possible? I think a MIN function on actualend & MAX on actualStart maybe needed?
Thanks
Andrew
Are you free now? If so, how do we connect?
I will be logged on again tomorrow morning - is 0930 UK time any good for you? If not then I'm flexible on times, whenever suits you best
10 Replies
ArchStanton , Try a new column like
a new measure
averageX(values(Table[OwnerID]), calculate(Datediff( Min(Table[Actual End]) , Max(Table[Actual Start]), day) )
or
Sumx(values(Table[OwnerID]), calculate(Datediff( Min(Table[Actual End]) , Max(Table[Actual Start]), day) )
Thanks, can this be done only when the regardingobjectid are the same on consecutive rows ELSE its a straightforward DATEDIFF?
ArchStanton can you share a sample date / sample file and and hard code / tell what are the numbers you want to get and where (measure / new column)
actualend actualstart ownerid pre_tasktype_display regardingobjectid Display statuscode_display Time in Deferral AP DAYSBETWEEN 10-Mar-22 08-Mar-22 1168d573-ee6e-ec11-8943-000d3a870615 Deferral 8ad8e4f8-e89b-ec11-b400-002248009118 Awaiting information Completed 3 3 NETWORKDAYS(B2,A2) 11-Apr-22 17-Mar-22 1168d573-ee6e-ec11-8943-000d3a870615 Deferral 8ad8e4f8-e89b-ec11-b400-002248009118 Awaiting information Completed 18 6 NETWORKDAYS(A2,B3) 13-May-22 26-Mar-22 1168d573-ee6e-ec11-8943-000d3a870616 Deferral 8ad8e4f8-e89b-ec11-b400-002248009117 Awaiting information Completed 33 35 NETWORKDAYS(B4,A4) ArchStanton I'm available now for a quick zoom / teams if you can
So the first 2 rows are from the same record but the 3rd one is different. Scroll to the end to see what the correct values are in the DAYSBETWEEN Column. Hope that makes sense
