cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Rajesh1 Regular Visitor
Regular Visitor

Max and Min date/times based on condition

hello all,

 

I have the following table:

 

v.ID.LocDateStatus
V1P01-Feb-2019 13:00:00Slow
V1P01-Feb-2019 12:30:00Slow
V1P01-Feb-2019 12:15:00Slow
V1P31-Jan-2019 23:00:00Slow
V1R31-Jan-2019 20:30:00Slow
V1R31-Jan-2019 18:00:00Slow
V1R30-Jan-2019 16:00:00Slow
V1R30-Jan-2019 14:00:00Slow
V1R30-Jan-2019 12:00:00Slow
V1R30-Jan-2019 11:00:00Normal
V1R30-Jan-2019 10:00:00Normal
V1R30-Jan-2019 09:00:00Normal
V1R30-Jan-2019 07:00:00Normal
V1R30-Jan-2019 06:30:00Slow
V1R30-Jan-2019 01:00:00Slow
V1R29-Jan-2019 23:00:00Slow
V1R29-Jan-2019 18:00:00Slow
V1R29-Jan-2019 15:00:00Slow

 

There will be more rows for different vehicle Ids (v. ID) but for I've limited it to one vehicle id.

The expected output is to identify the latest date/time and earliest date/time for vid+loc+status combination. Then, compute the difference in days for this combination. The expected output for the above should be as below:

 

 

v. ID.LocStatusFrom DateTo dateDays
V1PSlow31-Jan-2019 23:00:0001-Feb-2019 13:00:000.583333
V1RSlow30-Jan-2019 12:00:0031-Jan-2019 20:30:001.354167
V1RNormal30-Jan-2019 07:00:0030-Jan-2019 11:00:000.166667
V1RSlow29-Jan-2019 15:00:0030-Jan-2019 06:30:000.645833

 

I attempted various options like transpose, summarizecolumns with filter etc. but failed miserably.  Please can someone assist on how I am to go about achieving this.

 

thanks in advance.

Rajesh

2 REPLIES 2
Community Support Team
Community Support Team

Re: Max and Min date/times based on condition

Hi @Rajesh1 

Is there a column to define differnt vehicle running record as below?

v.ID. Loc Date Status column
V1 P 2/1/2019 13:00 Slow 1
V1 P 2/1/2019 12:30 Slow 1
V1 P 2/1/2019 12:15 Slow 1
V1 P 1/31/2019 23:00 Slow 1
V1 R 1/31/2019 20:30 Slow 2
V1 R 1/31/2019 18:00 Slow 2
V1 R 1/30/2019 16:00 Slow 2
V1 R 1/30/2019 14:00 Slow 2
V1 R 1/30/2019 12:00 Slow 2
V1 R 1/30/2019 11:00 Normal 3
V1 R 1/30/2019 10:00 Normal 3
V1 R 1/30/2019 9:00 Normal 3
V1 R 1/30/2019 7:00 Normal 3
V1 R 1/30/2019 6:30 Slow 4
V1 R 1/30/2019 1:00 Slow 4
V1 R 1/29/2019 23:00 Slow 4
V1 R 1/29/2019 18:00 Slow 4
V1 R 1/29/2019 15:00 Slow 4

From your screenshot and provided information, it is hard to differ the row 1 and row4.

v. ID. Loc Status From Date To date Days  
V1 P Slow ######## ######## 0.583333 1
V1 R Slow ######## ######## 1.354167 2
V1 R Normal ######## ######## 0.166667 3
V1 R Slow ######## ######## 0.645833 4

 

Best Regards

Maggie

Rajesh1 Regular Visitor
Regular Visitor

Re: Max and Min date/times based on condition

hi @v-juanli-msft ,

 

Thanks for your reply.

 

I have been unable to work out a way to create such a column. 
Tried Current_Column_Value = EARLIER(Column Value) in a calculate + filter.  I am yet to find a way to get this 'key' column created. If I am able to create this column, I can then do a summarizecolumn and get the job done.

This is where I am stuck at.

Looking forward to some ideas to achieve this.

 

Thanks again,

Rajesh

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 108 members 1,483 guests
Please welcome our newest community members: