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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ArchStanton
Continued Contributor
Continued Contributor

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)

 

ArchStanton_1-1659972879981.png

 

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

 

1 ACCEPTED SOLUTION
ArchStanton
Continued Contributor
Continued Contributor

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

View solution in original post

10 REPLIES 10
ArchStanton
Continued Contributor
Continued Contributor

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
Super User
Super User

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

 

SpartaBI
Community Champion
Community Champion

@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
Continued Contributor
Continued Contributor

actualendactualstartowneridpre_tasktype_displayregardingobjectidDisplaystatuscode_displayTime in Deferral APDAYSBETWEEN    
10-Mar-2208-Mar-221168d573-ee6e-ec11-8943-000d3a870615Deferral8ad8e4f8-e89b-ec11-b400-002248009118Awaiting informationCompleted33 NETWORKDAYS(B2,A2)
11-Apr-2217-Mar-221168d573-ee6e-ec11-8943-000d3a870615Deferral8ad8e4f8-e89b-ec11-b400-002248009118Awaiting informationCompleted186 NETWORKDAYS(A2,B3)
13-May-2226-Mar-221168d573-ee6e-ec11-8943-000d3a870616Deferral8ad8e4f8-e89b-ec11-b400-002248009117Awaiting informationCompleted3335 NETWORKDAYS(B4,A4)

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

ArchStanton
Continued Contributor
Continued Contributor

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?

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

ArchStanton
Continued Contributor
Continued Contributor

are you free today by any chance?

ArchStanton
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.