Determining the correct sales department

[EvgeniBB]

Hi, I must set in a table, who is the department of a merchant who made the sale during a certain period. The problem is that traders change their departments and I must comply with this, which trader in which department was exactly the month of sale.
For this purpose I have 2 tables (see the attached file) – one with sales by month and one, which is indicated on what date the trader has moved to a new department. My question is how do I show who's in charge of the trader in the period between the date of entry to his first manager and the date of entry into the second.

Your help will be highly appreciated

 

[horseyride]

I actually think all you need to do is highlight the Table1.New Regional Manager/ Strategic Manager column then right click and choose Fill...Down...

If for some reason you expect your data might be jumbled, try this which creates all possible date combinations for each sales person, that you can use to merge into your other table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiebelLOGIN", type text}, {"New Regional Manager/ Strategic Manager", type text}, {"Entry date", type date}, {"Month", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if Source{[Index]}[SiebelLOGIN] = Source{[Index]+1}[SiebelLOGIN] then Date.AddMonths(Date.FromText(Source{[Index]+1}[Month]),-1) else [Month] otherwise [Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Range", each {Number.From([Month])..Number.From([Custom])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom1", "Range"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Range",{{"Range", type date}})
in
    #"Changed Type2"

View attachment test-2.xlsx

 

[EvgeniBB]

I actually think all you need to do is highlight the Table1.New Regional Manager/ Strategic Manager column then right click and choose Fill...Down...

If for some reason you expect your data might be jumbled, try this which creates all possible date combinations for each sales person, that you can use to merge into your other table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiebelLOGIN", type text}, {"New Regional Manager/ Strategic Manager", type text}, {"Entry date", type date}, {"Month", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if Source{[Index]}[SiebelLOGIN] = Source{[Index]+1}[SiebelLOGIN] then Date.AddMonths(Date.FromText(Source{[Index]+1}[Month]),-1) else [Month] otherwise [Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Range", each {Number.From([Month])..Number.From([Custom])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom1", "Range"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Range",{{"Range", type date}})
in
    #"Changed Type2"

View attachment 8772

Thank you very much, but I can not understand why the sales of salesman 2, which were made in 2018/06, are not allocated to Manager 5, although salesman 2 is working with this manager, from the previous month 2018/05?

 

[EvgeniBB]

Thank you for the quick answer!
I am not sure, however, that sales of SALESMAN 2 from 1[SUP]st[/SUP] of June 2018, are allocated correctly to MANAGER 5, where he works from 6[SUP]th[/SUP] of May 2018. (attaching new file)

SiebelLOGIN	Month	sales	Table1.New Regional Manager/ Strategic Manager
Salesman 1	Feb-17	50	manager 1
Salesman 1	Apr-17	1	manager 1
Salesman 1	Jan-18	25	manager 1
Salesman 1	Jan-19	30	manager 2
Salesman 2	Feb-16	10	manager 3
Salesman 2	Jun-17	15	manager 3
Salesman 2	Dec-17	20	manager 3
Salesman 2	Jun-18	25

 

[horseyride]

I am not sure, however, that sales of SALESMAN 2 from 1[SUP]st[/SUP] of June 2018, are allocated correctly to MANAGER 5, where he works from 6[SUP]th[/SUP] of May 2018. (attaching new file)

Because we did not account for current date. If you want all manager relationship to continue onwards through current date then change query to read:



to be

    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if Source{[Index]}[SiebelLOGIN] = Source{[Index]+1}[SiebelLOGIN] then Date.AddMonths(Date.FromText(Source{[Index]+1}[Month]),-1) else DateTime.Date(DateTime.LocalNow()) otherwise DateTime.Date(DateTime.LocalNow())),

 

[EvgeniBB]

Thank you very much. I'll try.