cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArchStanton
Helper V
Helper V

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
Helper V
Helper V

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.