Transform data to understand seconds saved

Hello jcastr02,

Can you please try the following approach:

1. Create a Calculated Column for Start and End of Additional Support

StartSupportDate = 
CALCULATE(
    MIN(Table1[Month]),
    FILTER(
        Table1,
        Table1[Store Number] = EARLIER(Table1[Store Number]) &&
        Table1[Additional Support Provided] = "Y"
    )
)

EndSupportDate = 
CALCULATE(
    MAX(Table1[Month]),
    FILTER(
        Table1,
        Table1[Store Number] = EARLIER(Table1[Store Number]) &&
        Table1[Additional Support Provided] = "Y"
    )
)

2. Create a Measure to Calculate Seconds Improved

Seconds Improved = 
VAR StartHoldTime = 
    CALCULATE(
        MAX(Table1[Hold Time (seconds)]),
        FILTER(
            Table1,
            Table1[Store Number] = MAX(Table1[Store Number]) &&
            Table1[Month] = MINX(FILTER(Table1, Table1[Store Number] = MAX(Table1[Store Number]) && Table1[Additional Support Provided] = "Y"), Table1[Month])
        )
    )

VAR EndHoldTime = 
    CALCULATE(
        MAX(Table1[Hold Time (seconds)]),
        FILTER(
            Table1,
            Table1[Store Number] = MAX(Table1[Store Number]) &&
            Table1[Month] = MAXX(FILTER(Table1, Table1[Store Number] = MAX(Table1[Store Number]) && Table1[Additional Support Provided] = "Y"), Table1[Month])
        )
    )

RETURN 
IF(
    MAX(Table1[Month]) = MAXX(FILTER(Table1, Table1[Store Number] = MAX(Table1[Store Number]) && Table1[Additional Support Provided] = "Y"), Table1[Month]),
    StartHoldTime - EndHoldTime,
    BLANK()
)

Hope this helps!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLDoMwDATQq1RZI2GbOJ+jVIj7X6OEYFoYN4uwebJJRrOuQTVMgZe6f1/7iTPPQhLDNplFOk3RcrP3fhKamuU/O5sVtOMuzapj5TSmwUWZHWRDcVANu6m3VX4ttXVC9oxbbA9TNFEvtsdcBmNxY+u2mNXB3D22jnwhI9KVqTiT1hqGaJiLF9vRl/5HaFs3cdv2mEtoZHfJg50FLZlVtG+haLD0Hts5ubixddQLMZqEbds+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Number" = _t, #"Hold Time (seconds)" = _t, #"Additional Support Provided" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"Hold Time (seconds)", Int64.Type}, {"Additional Support Provided", type text}, {"Month", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number", "Additional Support Provided"}, {{"Rows", each _, type table [Store Number=nullable number, #"Hold Time (seconds)"=nullable number, Additional Support Provided=nullable text, Month=nullable date]}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Additional Support Provided] = "Y")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Seconds Improved", each Table.First([Rows])[#"Hold Time (seconds)"]-Table.Last([Rows])[#"Hold Time (seconds)"],Int64.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

In the linked-to workbook below, a Power Query query table output at cell L2.
https://app.box.com/s/wf6uvqoh7zbsqavimyfdgg4nla8b6aky

If there are more than one group of Ys, each has its improved seconds calculated. As a demonstration, if you remove the Y from July 2024 (cell D17) you get this:

Hi,

Thanks for the solutions p45cal , lbendlin  and Sahir_Maharaj  provided, and i want to offer some more information for user to refer to.

hello jcastr02 , you can create a blank query and input the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLDoMwDATQq1RZI2GbOJ+jVIj7X6OEYFoYN4uwebJJRrOuQTVMgZe6f1/7iTPPQhLDNplFOk3RcrP3fhKamuU/O5sVtOMuzapj5TSmwUWZHWRDcVANu6m3VX4ttXVC9oxbbA9TNFEvtsdcBmNxY+u2mNXB3D22jnwhI9KVqTiT1hqGaJiLF9vRl/5HaFs3cdv2mEtoZHfJg50FLZlVtG+haLD0Hts5ubixddQLMZqEbds+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Number" = _t, #"Hold Time (seconds)" = _t, #"Additional Support Provided" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"Hold Time (seconds)", Int64.Type}, {"Additional Support Provided", type text}, {"Month", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number", "Additional Support Provided"}, {{"Count", each _, type table [Store Number=nullable number, #"Hold Time (seconds)"=nullable number, Additional Support Provided=nullable text, Month=nullable date]}, {"Min_Month", each List.Min([Month]), type nullable date}, {"Max_Month", each List.Max([Month]), type nullable date}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Hold Time (seconds)", "Month"}, {"Hold Time (seconds)", "Month"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each let a=[Store Number],
b= Table.SelectRows(#"Expanded Count", each [Additional Support Provided]="Y" and [Month]=[Min_Month] and [Store Number]=a)[#"Hold Time (seconds)"]{0}
in if [Additional Support Provided]="Y" and [Month]=[Max_Month] then Number.Abs([#"Hold Time (seconds)"]-b) else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min_Month", "Max_Month"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi jcastr02, different approach:

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLDoMwDATQq1RZI2GbOJ+jVIj7X6OEYFoYN4uwebJJRrOuQTVMgZe6f1/7iTPPQhLDNplFOk3RcrP3fhKamuU/O5sVtOMuzapj5TSmwUWZHWRDcVANu6m3VX4ttXVC9oxbbA9TNFEvtsdcBmNxY+u2mNXB3D22jnwhI9KVqTiT1hqGaJiLF9vRl/5HaFs3cdv2mEtoZHfJg50FLZlVtG+haLD0Hts5ubixddQLMZqEbds+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Number" = _t, #"Hold Time (seconds)" = _t, #"Additional Support Provided" = _t, Month = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"Hold Time (seconds)", Int64.Type}, {"Additional Support Provided", type text}, {"Month", type date}}),
    GroupedRows = Table.Group(ChangedType, {"Store Number"}, {{"All", each 
        [ a = Table.SelectRows(_, (x)=> x[Additional Support Provided] = "Y"),
          b = Table.First(a)[#"Hold Time (seconds)"]? - Table.Last(a)[#"Hold Time (seconds)"]?,
          c = #table(type table[Store Number=Int64.Type , Reporting Month=date, Seconds Improved=Int64.Type], {{[Store Number]{0}?, Table.Last(a)[Month]?, b}})
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLDoMwDATQq1RZI2GbOJ+jVIj7X6OEYFoYN4uwebJJRrOuQTVMgZe6f1/7iTPPQhLDNplFOk3RcrP3fhKamuU/O5sVtOMuzapj5TSmwUWZHWRDcVANu6m3VX4ttXVC9oxbbA9TNFEvtsdcBmNxY+u2mNXB3D22jnwhI9KVqTiT1hqGaJiLF9vRl/5HaFs3cdv2mEtoZHfJg50FLZlVtG+haLD0Hts5ubixddQLMZqEbds+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Number" = _t, #"Hold Time (seconds)" = _t, #"Additional Support Provided" = _t, Month = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"Hold Time (seconds)", Int64.Type}, {"Additional Support Provided", type text}, {"Month", type date}}),
    GroupedRows = Table.Group(ChangedType, {"Store Number"}, {{"All", each 
        [ a = Table.AddIndexColumn(_, "IndexHelper", 1, 1, Int64.Type),
          b = Table.SelectRows(a, (x)=> x[Additional Support Provided] = "Y"),
          c = Table.First(b)[#"Hold Time (seconds)"]? - Table.Last(b)[#"Hold Time (seconds)"]?,
          d = Table.AddColumn(a, "Seconds Improved", (x)=> try (if x[IndexHelper] = Table.Last(b)[IndexHelper] then c else null) otherwise null, Int64.Type)
        ][d], type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All