cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Dates in period for a dynamic period

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

 

IncidentIdAccountNameAccountNumberAccountTypeCallDateCallTimeMajorCommentMinorCommentThirdTierAccountDuration
33059568Example Company98765432Customer04/12/201816:20:26PayrollRTIFPS728
33925147Example Company98765432Customer03/05/201909:50:39PayrollInstallation and ActivationInstall Program735
33928425Example Company98765432Customer03/05/201914:13:58PayrollInstallation and ActivationInstall Program828
33928851Example Company98765432Customer03/05/201914:46:08PayrollInstallation and ActivationInstall Program429
33929819Example Company98765432Customer03/05/201916:13:20PayrollInstallation and ActivationInstall Program1255
33930022Example Company98765432Customer03/05/201916:29:46PayrollInstallation and ActivationInstall Program561
33930533Example Company98765432Customer03/05/201917:40:37PayrollInstallation and ActivationInstall Program1952
33935776Example Company98765432Customer04/05/201915:52:54PayrollSetup and ManageEmployee206
33936491Example Company98765432Customer04/05/201916:58:43PayrollProcessingCorrecting a mistake3359
33942334Example Company98765432Customer05/05/201916:34:21PayrollProcessingCorrecting a mistake3767
33951852Example Company98765432Customer11/05/201913:32:33PayrollProcessingCalculations266
33935776Example Company98765432Customer10/06/201915:52:54PayrollSetup and ManageEmployee206

 

What I want to achieve in bold

 

IncidentIdAccountNameAccountNumberAccountTypeCallDateCallTimeMajorCommentMinorCommentThirdTierAccountDurationTotalCallsCallNumberTotalDurationSeqDurationFirstIncidentId
33059568Example Company98765432Customer04/12/201816:20:26PayrollRTIFPS7281172872833059568
33925147Example Company98765432Customer03/05/201909:50:39PayrollInstallation and ActivationInstall Program7351011335873533925147
33928425Example Company98765432Customer03/05/201914:13:58PayrollInstallation and ActivationInstall Program82810213358156333925147
33928851Example Company98765432Customer03/05/201914:46:08PayrollInstallation and ActivationInstall Program42910313358199233925147
33929819Example Company98765432Customer03/05/201916:13:20PayrollInstallation and ActivationInstall Program125510413358324733925147
33930022Example Company98765432Customer03/05/201916:29:46PayrollInstallation and ActivationInstall Program56110513358380833925147
33930533Example Company98765432Customer03/05/201917:40:37PayrollInstallation and ActivationInstall Program195210613358576033925147
33935776Example Company98765432Customer04/05/201915:52:54PayrollSetup and ManageEmployee20610713358596633925147
33936491Example Company98765432Customer04/05/201916:58:43PayrollProcessingCorrecting a mistake335910813358932533925147
33942334Example Company98765432Customer05/05/201916:34:21PayrollProcessingCorrecting a mistake3767109133581309233925147
33951852Example Company98765432Customer11/05/201913:32:33PayrollProcessingCalculations2661010133581335833925147
33935776Example Company98765432Customer10/06/201915:52:54PayrollSetup and ManageEmployee2061120620733935776

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

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

Capture.PNG

View solution in original post

Highlighted

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

View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

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

Capture.PNG

View solution in original post

Highlighted

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.

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.

Highlighted

@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.

Highlighted

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors