Calculate the time between dates on different rows

[ArchStanton]

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

[ArchStanton]

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

[amitchandak]

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

[ArchStanton]

Thanks, can this be done only when the regardingobjectid are the same on consecutive rows ELSE its a straightforward DATEDIFF?

[SpartaBI]

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

[ArchStanton]

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)

[SpartaBI]

@ArchStanton I'm available now for a quick zoom / teams if you can

[ArchStanton]

Thanks for the offer, I logged yesterday so just saw your message. I'll take you up on the call later today if thats ok?

[SpartaBI]

sure, ping me here. I'm availble now or in around 6 hours from now

[ArchStanton]

are you free today by any chance?

[ArchStanton · Accepted solution]

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