Search Table for datetime events and show durations

[Pick]

Hi,

   I have a table below.   This table is for one order only the full table has may order_nos.   I want to create a new table to show the duration between the earliest Start_Time of every Unit and and End_time based on a selectable CONTROL_STATEGY.  IN this way each Unit will have one row with a start time and end time.

  Thanks

   Ian

[richbenmintz]

Hi @Pick ,

Can you provide a couple things:

1. Sample Data Set

2. the expect result of the derived table.

You can accomplish this task either through data transformation in Power Query or the creation of a summary table using a Dax table formula

[Pick]

Hi,

  Hi Thanks for the reply.  Source data would be (but there would be multiple Order_no with same CONTROL_STRATEGYs

 Output Table would be as follows for each order_No:

  Thanks

   Ian

[amitchandak]

Min, max, firstdate, lastdate and on start_date and end_date should work as long as you have filter and context.

Max(start_date), min(start_date)

If need you can add context using values, allexcept or summarize 

like 

calculate(Max(start_date),values(table[CONTROL_STRATEGY]))

calculate(Max(start_date),allexcept(table,table[order-number],table[CONTROL_STRATEGY])

https://community.powerbi.com/t5/Desktop/Allexcept-from-two-tables/td-p/255925

meausre = maxx(Summarize(table,table,table[order-number],table[CONTROL_STRATEGY]),"_max",Max(start_date)),[_max])

[richbenmintz]

Hi @Pick 

One request, can you provide the data in a table and not a screen cap, i can then mock it up in a pbix file