Show initial value in Dax based on several conditions

[Dimi_2207]

Hi All,

Could you, please, help me with the following topic.

I have data of calls where i can see if the call is the repeated one (based on phone number) within 7 days.

For example, initial call from phone number 333333 happened on 09.09

second and third on 14.09 (5 days later) and

next one on 18.09. (4 days later). All these calls wich followed initial one are considered to be repeated in 7 days rolling cycle.

Another one from the same number happened 01.10 (that is 13 days later from the previous - so it's not a continuation of 7 days rolling cycle). 

I need a help with the last column - to display in DAX originator of the first call (initial agent) who triggered 7 days repeated calls cycle.

Please, see table below.

Thank you very much for your support on this matter!

Phone number	Call date and time	Days between call attempts from the same phone numbers	Count of call attempt if previous call happened in past 7 days	Repeated within 7 days period?	Agent	Previous call date-time if previous call happened in past 7 days	Previous agent  if previous call happened in past 7 days	Initial agent who handled 1st call  if previous call happened in past 7 days
111111	02-09-24 09:25		1	N	Pieter
222222	06-09-24 10:51		1	N	Ivan
333333	09-09-24 14:37		1	N	Stephane			Stephane
444444	10-09-24 15:58		1	N	Iris
555555	11-09-24 11:39		1	N	Marie
333333	14-09-24 15:41	5,0	2	Y	Marie	09-09-24 14:37	Stephane	Stephane
333333	14-09-24 18:57	0,1	3	Y	Iris	14-09-24 15:41	Marie	Stephane
666666	16-09-24 17:22		1	N	Stephane
777777	17-09-24 10:30		1	N	Marie
333333	18-09-24 15:49	3,9	4	Y	Dorien	14-09-24 18:57	Iris	Stephane
888888	26-09-24 15:38		1	N	Iris			Iris
888888	30-09-24 08:30	3,7	2	Y	Marie	26-09-24 15:38	Iris	Iris
777777	01-10-24 09:10	13,9	1	N	Stephane
888888	01-10-24 09:45	1,05	3	Y	Michael	30-09-24 08:30	Marie	Iris
333333	01-10-24 11:43		1	N	Ivan

[lbendlin]

Your sample data is insufficient to provide a proper solution.  Here's a starting point for 333333

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBbsMgEER/BXGOJTBgMLeovfQQKVKPVg60WdVIDqlc2u8vrAuOU1Wd4+ixs8wOAz2O1wAkfF5eYKY7+uCmiZxdBOLCmUR/gWTu3yBEetoNlKOSxdqG9U0rCettq5Jx9BBhJki1qEx1PxRnVuVnT18uICJQGekLIq3QyXiO8D66AIhJVHI5K5iyyuRJs/9Y0hQqM7ww3Io+GQc3eyDbPC7XQZL/Cxmr9CatQ2Wm/k1b/OxmcY3KmF4rEOyvPHOzVN788ZqgsFAGlcy2Wymx7aAyovTEzK+4uhPjTepzOR5n96vXWbeYzAUf/OvoYLo7YKFS61LUG5++AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).5" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Call date and time", type datetime}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Phone number"}, {{"Rows", each _, type table [Phone number=nullable text, Call date and time=nullable datetime, Agent=nullable text]}}),
    #"333333" = #"Grouped Rows"{[#"Phone number"="333333"]}[Rows],
    #"Sorted Rows" = Table.Sort(#"333333",{{"Call date and time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Days Between", each if [Index]=0 then null else Number.From([Call date and time]-#"Added Index"[Call date and time]{[Index]-1}), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Calls in Last 7 Days", (k)=> Table.RowCount(Table.SelectRows(#"Added Index",each [Call date and time]<=k[Call date and time] and [Call date and time]>=k[Call date and time]-#duration(7,0,0,0))),Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Previous Call in Last 7 Days", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Call date and time]{[Index]-1} else null, type datetime),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Previous Agent if in past 7 days", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Agent]{[Index]-1} else null,type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Initial Agent", each List.Accumulate({0..[Index]},#"Added Index"{0}[Agent],(state,current)=> if (#"Added Custom"[Days Between]{current} ?? 0 ) <=7 then state else null),type text)
in
    #"Added Custom4"

Please provide sample data that fully covers your issue.

[Dimi_2207]

Thank you very much for the proposed code!

Please, accept, extended sample with more data in excel: https://docs.google.com/spreadsheets/d/1MogqRB-GIkEvPV0iMJu0S_-RoKqN8AWA/edit?usp=sharing&ouid=10325...

Could you, please, help so it works with this bigger sample and different phone numbers? 

Thank you !!

[lbendlin]

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Extended Sample.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Phone number", type text}, {"Call date and time", type datetime}, {"Agent", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Phone number] <> null),
    Processed = (tbl) => let 
        #"Sorted Rows" = Table.Sort(tbl,{{"Call date and time", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Days Between", each if [Index]=0 then null else Number.From([Call date and time]-#"Added Index"[Call date and time]{[Index]-1}),type number),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Calls in Past 7", (k)=> Table.RowCount(Table.SelectRows(#"Added Index",each [Call date and time]<=k[Call date and time] and [Call date and time]>=k[Call date and time]-#duration(7,0,0,0))), Int64.Type),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Previous Call in Past 7", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Call date and time]{[Index]-1} else null, type datetime),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Previous Agent in Past 7", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Agent]{[Index]-1} else null, type text),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Initial Agent", each List.Accumulate({0..[Index]},#"Added Index"{0}[Agent],(state,current)=> if (#"Added Custom"[Days Between]{current} ?? 0 ) <=7 then state else null))
    in
        #"Added Custom4",
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Phone number"}, {{"Rows", each _, type table [Phone number=nullable text, Call date and time=nullable datetime, Agent=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Processed([Rows])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Phone number", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Call date and time", "Agent", "Days Between", "Calls in Past 7", "Previous Call in Past 7", "Previous Agent in Past 7", "Initial Agent"}, {"Call date and time", "Agent", "Days Between", "Calls in Past 7", "Previous Call in Past 7", "Previous Agent in Past 7", "Initial Agent"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Call date and time", type datetime}})
in
    #"Changed Type1"

Note :  you need to filter out all the empty rows!

Please explain how you would want to handle 15615. Who would be the Initial Agent? Still Katty? Or Dorien?

[Dimi_2207]

In this example - Dorien, because this call happened later than 7 days after the one that was handled by Katty

[lbendlin · Accepted solution]

let
    Source = Excel.Workbook(File.Contents("C:\Users\xx\Downloads\Extended Sample.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Phone number", type text}, {"Call date and time", type datetime}, {"Agent", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Phone number] <> null),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Phone number] = "15615"),
    Processed = (tbl) => let 
        #"Sorted Rows" = Table.Sort(tbl,{{"Call date and time", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Days Between", each if [Index]=0 then null else Number.From([Call date and time]-#"Added Index"[Call date and time]{[Index]-1}),type number),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Calls in Past 7", (k)=> Table.RowCount(Table.SelectRows(#"Added Index",each [Call date and time]<=k[Call date and time] and [Call date and time]>=k[Call date and time]-#duration(7,0,0,0))), Int64.Type),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Previous Call in Past 7", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Call date and time]{[Index]-1} else null, type datetime),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Previous Agent in Past 7", each if ([Days Between] ?? 8 ) <=7 then #"Added Custom"[Agent]{[Index]-1} else null, type text),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Initial Agent", each List.Accumulate({0..[Index]},#"Added Index"{0}[Agent],(state,current)=> if (#"Added Custom"[Days Between]{current} ?? 0 ) <=7 then state else #"Added Index"{current}[Agent]))
    in
        #"Added Custom4",
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Phone number"}, {{"Rows", each _, type table [Phone number=nullable text, Call date and time=nullable datetime, Agent=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Processed([Rows])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Phone number", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Call date and time", "Agent", "Days Between", "Calls in Past 7", "Previous Call in Past 7", "Previous Agent in Past 7", "Initial Agent"}, {"Call date and time", "Agent", "Days Between", "Calls in Past 7", "Previous Call in Past 7", "Previous Agent in Past 7", "Initial Agent"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Call date and time", type datetime}})
in
    #"Changed Type1"

[Dimi_2207]

Thank you very much for your help ! Works perfectly !