Transform data to understand seconds saved

[jcastr02]

I have a list of stores with Phone hold time per month.  We want to understand for stores that are provided additional support how many second savings we had after support was provided.  For example below - store 55 additional support started on 6/1/2024 and stopped 10/1/2024.  I'd like for it only calculate the difference between the start and end dates when the Additional support column is Yes and only put the value on the month where it stopped.  For example 6/1/2024 Hold time was 170 sec - 10/1/2024 HOld time was 138, thus the difference in values is 32 seconds (put on the 10/1/2024 month).  Does anyone know how to use power query or DAX to achieve this?  Thanks so much - see below desired results

Store Number	Hold Time (seconds)	Additional Support Provided	Month			Desired>>	Store Number	Reporting Month	Seconds Improved
55	139		4/1/2024				55	4/1/2024
55	140		5/1/2024				55	5/1/2024
55	170	Y	6/1/2024				55	6/1/2024
55	150	Y	7/1/2024				55	7/1/2024
55	140	Y	8/1/2024				55	8/1/2024
55	139	Y	9/1/2024				55	9/1/2024
55	138	Y	10/1/2024				55	10/1/2024	32	(170-138)
55	140		11/1/2024				55	11/1/2024
55	141		12/1/2024				55	12/1/2024
55	145		1/1/2025				55	1/1/2025
55	140		2/1/2025				55	2/1/2025
61	200	Y	4/1/2024				61	4/1/2024
61	200	Y	5/1/2024				61	5/1/2024
61	225	Y	6/1/2024				61	6/1/2024
61	200	Y	7/1/2024				61	7/1/2024
61	120	Y	8/1/2024				61	8/1/2024
61	130	Y	9/1/2024				61	9/1/2024
61	120	Y	10/1/2024				61	10/1/2024
61	110	Y	11/1/2024				61	11/1/2024	90	(200-110)
61	100		12/1/2024				61	12/1/2024
61	119		1/1/2025				61	1/1/2025
61	118		2/1/2025				61	2/1/2025
70	110		4/1/2024				70	4/1/2024
70	120		5/1/2024				70	5/1/2024
70	110		6/1/2024				70	6/1/2024
70	109		7/1/2024				70	7/1/2024
70	120		8/1/2024				70	8/1/2024
70	160		9/1/2024				70	9/1/2024
70	140		10/1/2024				70	10/1/2024
70	120		11/1/2024				70	11/1/2024
70	143		12/1/2024				70	12/1/2024
70	153		1/1/2025				70	1/1/2025
70	160		2/1/2025				70	2/1/2025

[Sahir_Maharaj · Accepted solution]

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!

[lbendlin · Accepted solution]

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.

[p45cal · Accepted solution]

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:

[v-xinruzhu-msft · Accepted solution]

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.

[dufoq3 · Accepted solution]

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

[p45cal · Accepted solution]

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:

[v-xinruzhu-msft · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[Sahir_Maharaj · Accepted solution]

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!