Calculate days between manufacturing steps

I’m working with manufacturing data, and I’d like to calculate the number of days that take place between each of the steps (sequences) in the manufacturing process. See the attached pbix. There is a single table loaded. The last column on this table is called “Days in Sequence”. This is where we want to calculate how many days were spent in this sequence.

Each job ID contains multiple sequences. We want to look at these “days in sequence” values based on each Job ID.

We would like to create a calculated column that looks at each Job ID, then calculates the number of days that pass before the next sequence starts, using the “Change Sequence Date” column.

Here’s an example of the intended result (yellow highlighted column):
Days between Sequence.pbix (254.5 KB)

I made an attempt at the DAX but it’s not producing the result I’m looking for. See attached PBIX.

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve provided, it’s more suitable if it’s done via Power Query. Below is the M Code alongwith the screenshot of the final results provided for the reference -

let
    Source = Excel.Workbook(File.Contents("F:\ProgFiles\Dell\Downloads\Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job ID", Int64.Type}, {"Change Sequence Date", type date}, {"Department", type text}, {"Sequence ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Job ID", Order.Ascending}, {"Change Sequence Date", Order.Descending}, {"Sequence ID", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Job ID"}, {{"FullTable", each _, type table [Job ID=nullable number, Change Sequence Date=nullable date, Department=nullable text, Sequence ID=nullable number]}}),
    Custom1 = 
    Table.TransformColumns(
        #"Grouped Rows",
        {{"FullTable", each Table.AddIndexColumn(_, "Index")}}),
    #"Added Custom" = 
    Table.AddColumn(Custom1, "Days In Sequence", each 
    let 
        AllDataTable = [FullTable],
        PrevRowValue = 
            Table.AddColumn(
            AllDataTable, "PrevValue", 
            each try AllDataTable [Change Sequence Date] {[Index] - 1} otherwise [Change Sequence Date] )
    in
        PrevRowValue),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Days In Sequence"}),
    #"Expanded Days In Sequence" = Table.ExpandTableColumn(#"Removed Other Columns", "Days In Sequence", {"Job ID", "Change Sequence Date", "Department", "Sequence ID", "PrevValue"}, {"Job ID", "Change Sequence Date", "Department", "Sequence ID", "PrevValue"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Days In Sequence",{{"Job ID", Int64.Type}, {"Change Sequence Date", type date}, {"Department", type text}, {"Sequence ID", Int64.Type}, {"PrevValue", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([PrevValue] - [Change Sequence Date]), Int64.Type),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Date Subtraction",{"Job ID", "Change Sequence Date", "Department", "Sequence ID", "Subtraction"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Other Columns1",{{"Job ID", Order.Ascending}, {"Change Sequence Date", Order.Ascending}, {"Sequence ID", Order.Ascending}})
in
    #"Sorted Rows1"

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Days Between Sequence - Harsh.pbix (259.8 KB)

Amazing, thank you!

Hi @pete.langlois

As with many things in Power BI, there are often multiple ways of accomplishing the same thing. Here’s another solution to your issue, this time using DAX and a simple modification of the [Previous Row Value] pattern:

This returns the desired result for [Job ID] 4314879 as per your example, but falls down on others, as there are cases where there are multiple records in your dataset for each job ID-sequence ID combination.

Hope this helps.
Greg
eDNA Forum - Days between Transactions.pbix (882.0 KB)