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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ArchStanton
Post Prodigy
Post Prodigy

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

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
Post Prodigy
Post Prodigy

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)

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

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

are you free today by any chance?

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors