Dynamic sum Datetime difference

TaroGulati 

pls see if this is what you want

Column =

VAR _start=CALCULATE(min('Table'[Start Date]),ALLEXCEPT('Table','Table'[Type]))

var _end=CALCULATE(MIN('Table'[End Date]),ALLEXCEPT('Table','Table'[Type]))

return _end-_start

 

pls see the attachment below

 

Hi TaroGulati ,

Here’s another solution using Power Query to calculate the sum of differences between the minimum Start Date for Step = A and minimum End Date for Step = B, grouped by Type. 

The goal is to calculate the time difference between the earliest Start Date for Step = A and the earliest End Date for Step = B for each unique Type. The dataset has rows with blank values in either the Start Date or End Date, making it challenging to pick the correct records for the calculation.

We solve this by using Power Query in Power BI, grouping data by Type and calculating the differences directly.

let
    Source = YourTable,
    
    // Convert Start Date and End Date to datetime format
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Start Date", type datetime}, {"End Date", type datetime}}),
    
    // Filter rows for Step A and Step B
    FilteredRows = Table.SelectRows(#"Changed Type", each [Step] = "A" or [Step] = "B"),
    
    // Group by Type and get the Min Start Date for Step A and Min End Date for Step B
    GroupedTable = Table.Group(
        FilteredRows,
        {"Type"},
        {
            {"Min Start Date", each List.Min(Table.SelectRows(_, each [Step] = "A")[Start Date]), type datetime},
            {"Min End Date", each List.Min(Table.SelectRows(_, each [Step] = "B")[End Date]), type datetime}
        }
    ),
    
    // Calculate the time difference in minutes
    AddedDuration = Table.AddColumn(
        GroupedTable,
        "Difference (Minutes)",
        each Duration.TotalMinutes([Min End Date] - [Min Start Date]),
        type number
    )
in
    AddedDuration

I have attached an example pbix file for your reference.

Best regards,