Dates in period for a dynamic period

[mark_carlisle]

I want to calculate the number of times a customer has called our support line after the initial call.

Example

Example Company calls for support on 04/12/2018, and does not call again within 7 days, this is 1 call.

Example Company calls for support again on 03/05/2019 a total of 6 times and also on the 04/05/2019 2 times, 05/05/2019 1 time, and 11/05/2019 1 time. This should count as 10 calls because although the first call took place on the 03/05/2019 and 7 days after would be the 10/05/2019 the period should be dynamic to include the 11/05/2019 as that was within 7 days of 05/05/2019 which was within 7 days of 03/05/2019

Example Company calls for support again on 10/06/2019, and does not call again within 7 days, this is 1 call.

Current Data

IncidentId	AccountName	AccountNumber	AccountType	CallDate	CallTime	MajorComment	MinorComment	ThirdTier	AccountDuration
33059568	Example Company	98765432	Customer	04/12/2018	16:20:26	Payroll	RTI	FPS	728
33925147	Example Company	98765432	Customer	03/05/2019	09:50:39	Payroll	Installation and Activation	Install Program	735
33928425	Example Company	98765432	Customer	03/05/2019	14:13:58	Payroll	Installation and Activation	Install Program	828
33928851	Example Company	98765432	Customer	03/05/2019	14:46:08	Payroll	Installation and Activation	Install Program	429
33929819	Example Company	98765432	Customer	03/05/2019	16:13:20	Payroll	Installation and Activation	Install Program	1255
33930022	Example Company	98765432	Customer	03/05/2019	16:29:46	Payroll	Installation and Activation	Install Program	561
33930533	Example Company	98765432	Customer	03/05/2019	17:40:37	Payroll	Installation and Activation	Install Program	1952
33935776	Example Company	98765432	Customer	04/05/2019	15:52:54	Payroll	Setup and Manage	Employee	206
33936491	Example Company	98765432	Customer	04/05/2019	16:58:43	Payroll	Processing	Correcting a mistake	3359
33942334	Example Company	98765432	Customer	05/05/2019	16:34:21	Payroll	Processing	Correcting a mistake	3767
33951852	Example Company	98765432	Customer	11/05/2019	13:32:33	Payroll	Processing	Calculations	266
33935776	Example Company	98765432	Customer	10/06/2019	15:52:54	Payroll	Setup and Manage	Employee	206

What I want to achieve in bold

IncidentId	AccountName	AccountNumber	AccountType	CallDate	CallTime	MajorComment	MinorComment	ThirdTier	AccountDuration	TotalCalls	CallNumber	TotalDuration	SeqDuration	FirstIncidentId
33059568	Example Company	98765432	Customer	04/12/2018	16:20:26	Payroll	RTI	FPS	728	1	1	728	728	33059568
33925147	Example Company	98765432	Customer	03/05/2019	09:50:39	Payroll	Installation and Activation	Install Program	735	10	1	13358	735	33925147
33928425	Example Company	98765432	Customer	03/05/2019	14:13:58	Payroll	Installation and Activation	Install Program	828	10	2	13358	1563	33925147
33928851	Example Company	98765432	Customer	03/05/2019	14:46:08	Payroll	Installation and Activation	Install Program	429	10	3	13358	1992	33925147
33929819	Example Company	98765432	Customer	03/05/2019	16:13:20	Payroll	Installation and Activation	Install Program	1255	10	4	13358	3247	33925147
33930022	Example Company	98765432	Customer	03/05/2019	16:29:46	Payroll	Installation and Activation	Install Program	561	10	5	13358	3808	33925147
33930533	Example Company	98765432	Customer	03/05/2019	17:40:37	Payroll	Installation and Activation	Install Program	1952	10	6	13358	5760	33925147
33935776	Example Company	98765432	Customer	04/05/2019	15:52:54	Payroll	Setup and Manage	Employee	206	10	7	13358	5966	33925147
33936491	Example Company	98765432	Customer	04/05/2019	16:58:43	Payroll	Processing	Correcting a mistake	3359	10	8	13358	9325	33925147
33942334	Example Company	98765432	Customer	05/05/2019	16:34:21	Payroll	Processing	Correcting a mistake	3767	10	9	13358	13092	33925147
33951852	Example Company	98765432	Customer	11/05/2019	13:32:33	Payroll	Processing	Calculations	266	10	10	13358	13358	33925147
33935776	Example Company	98765432	Customer	10/06/2019	15:52:54	Payroll	Setup and Manage	Employee	206	1	1	206	207	33935776

[Nolock · Accepted solution]

Hi @mark_carlisle,

here is a solution for you. It seaches recursively for all incident within of plus minus 7 days for an accountId and then aggregates the data for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRLi9swEADgv2JyXoI0oxk9bmVpYQ+F0O1tyUGkJoT6EWynNP++kh27cjEFxweLkR/yx2hGHx87REGW2Oxedp9/+/Ja5NlrXV59dQ93rNFMCiGEr7e2q8u8CaFQewl7EDJ+JNmBcMAhPPh7UxdFiL59fwvjl8N7GDX0rz2uYTaM06+PL9FhgaTSaxy4FxQdNk6sI+HQzhxvVdv5ovDdpa4yX/3IPp26y69++vdpdmjqc+PL6EKKTjGCEclMtyfhyDUK6FmuVE6i61d/nmuG1EYuJFxJjIteQ3KDV7ET27wK7OjF1GstLHmt6X/9nJdjfkFs8kqgqR5UAkbo6/RfMAoBsAEMNuR4E5hYjl5KvUaYRS8hPu3VToV+09sSbAlGMCdg0iyWwKQ1rzuoEjA5AkdqBn7Pu9u1l371lT/ncfGwdH3PYwiCR51OdZZ5ScfKruovNd9+Mk7hTBcSdcrb9lKd45d10+QhndU581l5Can8mfcKmprKJEiLsHRoKUBUa5A0R6JyINcjNesRadPOR7HY+iQNrekkKVMlOgSH/0ulL063oUrbuM08bfMwTLpH+2wsQyn2greX4eMaZiAeJ9CAOR7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IncidentId = _t, AccountName = _t, AccountNumber = _t, AccountType = _t, CallDate = _t, CallTime = _t, MajorComment = _t, MinorComment = _t, ThirdTier = _t, AccountDuration = _t, TotalCalls = _t, CallNumber = _t, TotalDuration = _t, SeqDuration = _t, FirstIncidentId = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"AccountName", "AccountType", "MajorComment", "MinorComment", "ThirdTier", "CallTime", "TotalCalls", "CallNumber", "TotalDuration", "SeqDuration", "FirstIncidentId"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"CallDate", type date}}, "de-DE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"AccountDuration", Int64.Type}}),

    fnGetAllCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date, curIncidentIds as list) as table =>
        let
            // filter out all rows having max distance of 7 days of the same AccountNumber
            FilteredTable = Table.SelectRows(
                tbl,
                each 
                    [AccountNumber] = accountNumber 
                    and Number.Abs(Duration.Days([CallDate] - curDate)) <= 7
                    and not List.Contains(curIncidentIds, [IncidentId])
            ),
            Result = 
                if Table.IsEmpty(FilteredTable) then
                    FilteredTable // return from recursion
                else
                    let 
                        // a list of all visited incidents
                        newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}),
                        // recursive all incidents before minimum  CallDate of already found incidents
                        checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDays(
                            tbl, 
                            accountNumber, 
                            Table.Min(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // recursive all incidents after maximum  CallDate of already found incidents
                        checkAfterCurrentDate = @fnGetAllCallsWithinSevenDays(
                            tbl, 
                            accountNumber, 
                            Table.Max(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // combine all tables coming from recursion
                        combinedResult = Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate})
                    in
                        combinedResult
        in
            Result,

    // unique records
    fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table =>
        let  
            distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDays(tbl, accountNumber, curDate, {}), "IncidentId"),
            indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1)
        in
            indexedTable,
    
    // unique records within seven days
    runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])),

    // aggregations
    TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type),
    CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]),
    TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))),
    SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])),
    FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId")))
in
    FirstIncident

[Nolock · Accepted solution]

Hi @mark_carlisle,

here is a solution for you. It seaches recursively for all incident within of plus minus 7 days for an accountId and then aggregates the data for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRLi9swEADgv2JyXoI0oxk9bmVpYQ+F0O1tyUGkJoT6EWynNP++kh27cjEFxweLkR/yx2hGHx87REGW2Oxedp9/+/Ja5NlrXV59dQ93rNFMCiGEr7e2q8u8CaFQewl7EDJ+JNmBcMAhPPh7UxdFiL59fwvjl8N7GDX0rz2uYTaM06+PL9FhgaTSaxy4FxQdNk6sI+HQzhxvVdv5ovDdpa4yX/3IPp26y69++vdpdmjqc+PL6EKKTjGCEclMtyfhyDUK6FmuVE6i61d/nmuG1EYuJFxJjIteQ3KDV7ET27wK7OjF1GstLHmt6X/9nJdjfkFs8kqgqR5UAkbo6/RfMAoBsAEMNuR4E5hYjl5KvUaYRS8hPu3VToV+09sSbAlGMCdg0iyWwKQ1rzuoEjA5AkdqBn7Pu9u1l371lT/ncfGwdH3PYwiCR51OdZZ5ScfKruovNd9+Mk7hTBcSdcrb9lKd45d10+QhndU581l5Can8mfcKmprKJEiLsHRoKUBUa5A0R6JyINcjNesRadPOR7HY+iQNrekkKVMlOgSH/0ulL063oUrbuM08bfMwTLpH+2wsQyn2greX4eMaZiAeJ9CAOR7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IncidentId = _t, AccountName = _t, AccountNumber = _t, AccountType = _t, CallDate = _t, CallTime = _t, MajorComment = _t, MinorComment = _t, ThirdTier = _t, AccountDuration = _t, TotalCalls = _t, CallNumber = _t, TotalDuration = _t, SeqDuration = _t, FirstIncidentId = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"AccountName", "AccountType", "MajorComment", "MinorComment", "ThirdTier", "CallTime", "TotalCalls", "CallNumber", "TotalDuration", "SeqDuration", "FirstIncidentId"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"CallDate", type date}}, "de-DE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"AccountDuration", Int64.Type}}),

    fnGetAllCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date, curIncidentIds as list) as table =>
        let
            // filter out all rows having max distance of 7 days of the same AccountNumber
            FilteredTable = Table.SelectRows(
                tbl,
                each 
                    [AccountNumber] = accountNumber 
                    and Number.Abs(Duration.Days([CallDate] - curDate)) <= 7
                    and not List.Contains(curIncidentIds, [IncidentId])
            ),
            Result = 
                if Table.IsEmpty(FilteredTable) then
                    FilteredTable // return from recursion
                else
                    let 
                        // a list of all visited incidents
                        newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}),
                        // recursive all incidents before minimum  CallDate of already found incidents
                        checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDays(
                            tbl, 
                            accountNumber, 
                            Table.Min(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // recursive all incidents after maximum  CallDate of already found incidents
                        checkAfterCurrentDate = @fnGetAllCallsWithinSevenDays(
                            tbl, 
                            accountNumber, 
                            Table.Max(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // combine all tables coming from recursion
                        combinedResult = Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate})
                    in
                        combinedResult
        in
            Result,

    // unique records
    fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table =>
        let  
            distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDays(tbl, accountNumber, curDate, {}), "IncidentId"),
            indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1)
        in
            indexedTable,
    
    // unique records within seven days
    runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])),

    // aggregations
    TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type),
    CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]),
    TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))),
    SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])),
    FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId")))
in
    FirstIncident

[mark_carlisle]

@Nolock I've been testing this with actual data around 130,000 rows (for a month), although ideally I'd like to bring in a years worth of data (~1.7 million rows) and the query never loads, or at least not in a reasonable time.

Are there any tips on improving performance on this or am I attempting too much with Power Query given the volume of data.

[Nolock · Accepted solution]

Hi @mark_carlisle,

I have some optimization ideas, please test it with your big table or publish a csv file with needed columns {"IncidentId", "AccountDuration", "CallDate", "AccountNumber"}. They don't contain any sensitive data.

What is improved:

•  the input table for fnGetAllCallsWithinSevenDaysOfOneAccount is now filtered - I only take rows for current AccountNumber and select only columns, which I later need.
•  there is also an idea in code: // NOTE: if you knew that no sequence of incidents is longer than i.e. +-2 month, you can also dramaticaly reduce the searched space and therefore increase performance 

Another ideas:

• store all incidentIds for every account which have been already processed in some previous steps => you don't need to calculate everything once again for every incident in a row. In your sample data it'll be 1 + 2 + 1 calls instead of 12.
• you can also try to use SQL Server Analysis Services or Azure Analysis Services instead of Power BI - the underlying technology is the same and you can everywhere use your PowerQuery queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRLi9swEADgv2JyXoI0oxk9bmVpYQ+F0O1tyUGkJoT6EWynNP++kh27cjEFxweLkR/yx2hGHx87REGW2Oxedp9/+/Ja5NlrXV59dQ93rNFMCiGEr7e2q8u8CaFQewl7EDJ+JNmBcMAhPPh7UxdFiL59fwvjl8N7GDX0rz2uYTaM06+PL9FhgaTSaxy4FxQdNk6sI+HQzhxvVdv5ovDdpa4yX/3IPp26y69++vdpdmjqc+PL6EKKTjGCEclMtyfhyDUK6FmuVE6i61d/nmuG1EYuJFxJjIteQ3KDV7ET27wK7OjF1GstLHmt6X/9nJdjfkFs8kqgqR5UAkbo6/RfMAoBsAEMNuR4E5hYjl5KvUaYRS8hPu3VToV+09sSbAlGMCdg0iyWwKQ1rzuoEjA5AkdqBn7Pu9u1l371lT/ncfGwdH3PYwiCR51OdZZ5ScfKruovNd9+Mk7hTBcSdcrb9lKd45d10+QhndU581l5Can8mfcKmprKJEiLsHRoKUBUa5A0R6JyINcjNesRadPOR7HY+iQNrekkKVMlOgSH/0ulL063oUrbuM08bfMwTLpH+2wsQyn2greX4eMaZiAeJ9CAOR7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IncidentId = _t, AccountName = _t, AccountNumber = _t, AccountType = _t, CallDate = _t, CallTime = _t, MajorComment = _t, MinorComment = _t, ThirdTier = _t, AccountDuration = _t, TotalCalls = _t, CallNumber = _t, TotalDuration = _t, SeqDuration = _t, FirstIncidentId = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"AccountName", "AccountType", "MajorComment", "MinorComment", "ThirdTier", "CallTime", "TotalCalls", "CallNumber", "TotalDuration", "SeqDuration", "FirstIncidentId"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"CallDate", type date}}, "de-DE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"AccountDuration", Int64.Type}}),

    fnGetAllCallsWithinSevenDaysOfOneAccount = (tbl as table, curDate as date, curIncidentIds as list) as table =>
        let
            // filter out all rows having max distance of 7 days of the same AccountNumber
            FilteredTable = Table.SelectRows(
                tbl,
                each 
                    Number.Abs(Duration.Days([CallDate] - curDate)) <= 7
                    and not List.Contains(curIncidentIds, [IncidentId])
            ),
            Result = 
                if Table.IsEmpty(FilteredTable) then
                    FilteredTable // return from recursion
                else
                    let 
                        // a list of all visited incidents
                        newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}),
                        // recursive all incidents before minimum CallDate of already found incidents
                        checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount(
                            tbl, 
                            Table.Min(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // recursive all incidents after maximum  CallDate of already found incidents
                        checkAfterCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount(
                            tbl, 
                            Table.Max(FilteredTable, "CallDate")[CallDate],
                            newIncidentIds
                        ),
                        // combine all tables coming from recursion
                        combinedResult = Table.Distinct(Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate}))
                    in
                        combinedResult
        in
            Result,

    // unique records
    fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table =>
        let  
            filteredRowsByAccountNumber = Table.SelectRows(tbl, each [AccountNumber] = accountNumber),
            filteredColumns = Table.SelectColumns(filteredRowsByAccountNumber, {"IncidentId", "AccountDuration", "CallDate"}),
            
            // NOTE: if you knew that no sequence of incidents is longer than i.e. +-2 month, you can also dramaticaly reduce the searched space and therefore increase performance
            filteredRowsByDate = filteredColumns, // Table.SelectRows(filteredColumns, each [CallDate] > Date.AddMonths(curDate, -2) and [CallDate] < Date.AddMonths(curDate, 2)), 
            
            distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDaysOfOneAccount(filteredRowsByDate, curDate, {}), "IncidentId"),
            indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1)
        in
            indexedTable,
    
    // unique records within seven days
    runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])),

    // aggregations
    TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type),
    CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]),
    TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))),
    SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])),
    FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId")))
in
    FirstIncident

[mark_carlisle]

That's magic. Thanks for the assistance.

Could I ask you for some pointers on resources for learning Power Query, specifically functions. I've used them for other things but nothing like this.

[Nolock]

Hi @mark_carlisle,

I've read this blog as introduction: https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi... and the rest is just imagination and solving real-life problems at work and on this forum.